Context Search

Hello,

I have a web application that I need to search based on what the user entered in the input box.
e.g when the user enters in the box something like "Brain Boom"

I need to search the column in the DB table where there is anything word like
Brain or has Boom or all the above. How will I accomplish this?

Thanks

In transact-sql the query would look something like this

select * from sometable where seachcolumn like '%Brain%' or searchcolumn like '%Boom%'

This will give you all colums in records from sometable that has Brain or Boom in column named searchcolumn.

|||Since these values are in one Text box, How would I know that there are two words in the text box? Do I have to always loop through the text box to check if it is a tab/comma delimited list?|||

Yes,

T-SQL is not able to determine that by itself. You need to construct a proper query for it and execute it.

I am not sure if full-text search capabilities would be an option in this case. Maybe some other more skilled SQL developer are able to give you more options.

|||

Two possible solutions that I would use.

1. Full Text Search. This sounds like a very good case for using it. It allows you to just say:

where CONTAINS ( columnName, 'Brain Boom')

It also gives you lots of other powerful features. I would almost certainly suggest this method based on what you have told us...

2. Check the techniques here: http://www.sommarskog.se/arrays-in-sql.html

then you can take the string 'Brain Boom' and put it in a table form like:

value
--
Brain
Boom

Then join to the table

select key, count(*)
from table
join <tableofvalues> as tbl
on table.columnName like '%' + tbl.value + '%'
group by key

Then you can see the rows that have the most matches.

Constraint question

I have 'Table1' with two fields:
- RecID
- POID
I want to allow records to be entered in Table1 where the same RecID and
POID can exist across multiple records in Table1, but I don't want to allow
a POID to exist in Table1 twice with different RecIDs.
This is OK
RecID | POID
1 | 100
1 | 100
This is not OK
RecID | POID
1 | 100
2 | 100
What's the best way to set this up?
Thanks,
CB1. add another table with constraints PK(RecID, POID ) and
UNIQUE(POID) or vice versa
2. add a FK to table1 referencing that another table (RecID, POID )
P.S. there could be a problem with your database design. you might want
to post your DDL|||On Mon, 3 Oct 2005 13:28:22 -0400, Chris Burgess wrote:
(snip)
>This is OK
>RecID | POID
>1 | 100
>1 | 100
Hi Chris,
No it's not. Unless you have more columns in the table, but omitted them
in this post.
But with this design, you've now got two exact duplicates. And SQL gives
you no way to address just one of them. Every change you wish to make to
one of these rows will be made to the other as well.
If the reality you are modeling has two entities with RecID 1 and POID 1
and you've got a way to distinguish them, then add an extra column to
store whatever distinguishes the two in your business. If you've got the
both of them, they are really indistiguishable, but you still need to
store the fact that there are two of them, add a columns to hold the
current number. That solves your constraint problem as well:
CREATE TABLE YourTable
(RecID int NOT NULL,
POID int NOT NULL,
NumberOfItems int NOT NULL DEFAULT 1,
PRIMARY KEY (POID),
CHECK (NumberOfItems >= 1)
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.