Using MS SQL Like Queries

[Newsgroup_User]

Hi im running a MS sql database behind my Cold fusion site.

I have a question on like queries that someone here will probably be able to
help me
I have a query that uses the following code

name like '#search#%'


The word Danger Sign appears as a name in my database.

When I do a search on Danger Signs no records are returned.

However when i do a search on Danger Sign my records are returned.


I also tried to use

name like '%#search#%'

which also returned no results on Danger Signs

Is the fact that my search term is two words causing the problem?

Thanks in advance

[The_ScareCrow]

If the 2 words "Danger Sign" are in the field and you search for "Danger Signs" then no it will not match it because it does not match.

To be able to do something like you want to do you will need to look into "full text searching" for sql server.

Ken

[Dan_Bracuk]

Where is the search variable coming from? If it's a url, and it wasn't encoded properly, the space might cause a problem. Turn on debugging and look at sql sent to the db.

[Newsgroup_User]

The ScareCrow wrote:
> If the 2 words "Danger Sign" are in the field and you search for "Danger Signs"
> then no it will not match it because it does not match.

ken, actually i think that should work. i just tried

SELECT uniLanguage
FROM unicodeTest
WHERE uniLanguage LIKE 'Chinese (%'

and it correctly returned 2 rows (Chinese (Traditional) & Chinese (Simplified)).

maybe the search term is getting bunged up?

> To be able to do something like you want to do you will need to look into
> "full text searching" for sql server.

that's good advice. using wild cards in LIKE where clauses makes sql server do a
table scan instead of using an index (unless the wild card is at the end of the
search term). ms's full text indexing wins awards for it's search. i normally
use it instead of verity.

[The_ScareCrow]

Paul,
Either, I have misunderstood this or you have ?

I read the post as he was trying to find the string (forget danger)

"signs"

In a string that only contains "sign"

Thus, let's assume the string is

"This Danger Sign is red"

Then if you try a search for

Like 'Danger Signs%'

It will not find it because the word signs is not in the string.

Ken

[Newsgroup_User]

The ScareCrow wrote:
> Either, I have misssunderstood this or you have ?

yup, i have.