Skip to main content
N
Newsgroup_User
Inspiring
September 21, 2006
Question

Using MS SQL Like Queries

  • September 21, 2006
  • 3 replies
  • 409 views
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


This topic has been closed for replies.

3 replies

N
Newsgroup_UserAuthor
Inspiring
September 22, 2006
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.
T
The_ScareCrow
Inspiring
September 22, 2006
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
D
Dan_Bracuk
Inspiring
September 22, 2006
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.
T
The_ScareCrow
Inspiring
September 21, 2006
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
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices