Using LIKE Wildcards in my queries

I have my search working nicely, but noticed the following:

search for city of 'fort lauderdale' and it returns the 5 results with 'fort lauderdale' entered as the field value.  Why doesnt it return the entries that have 'ft lauderdale' and 'Ft. lauderdale'?

my query line is pasted in code below:
AND tbl_cs_seeker.skr_citySeek LIKE <cfqueryparam value="%#form.sr_city#%" cfsqltype="cf_sql_varchar">

Open in new window

Who is Participating?

%Smith%   will allow any characters at BOT ends

Smith% allows any characters at the end, but the phrase must start with Smith

%Smith allows any characters at the beginning, but the phrase must end with Smith

This is a SQL question, you need to use LIKE when using a wildcard, they go together.   If you use = equall then it will look for the wild card character litteraly, not as a wild card as in 100%  
string comparisons are case sensitive and very specific.  "Ft."  is different case than "ft"  and it has a period in it too

you could try something like this...

lower(skr_citySeek) like 'f%t%lauderdale%'

"Like" means that where there is a wild card %  you can substitute any characters.

WHERE NAME like '%Smith%'

where match

Mr. Smith
John Smith Jr
Smith, Steve

It does not have the ability to translate similar words, abbreviations, etc..
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

More more intelligent searches like this you could try Verity which comes with Coldfusion

It does text searches, like you would find in Google, etc..
Patrick MatthewsCommented:
Because "ft lauderdale' and 'ft. lauderdale' do not meet the criterion "Like '%fort lauderdale%'"

If you pass in just lauderdale as the parameter, that will match all three.  So will f%t%lauderdale.
Patrick MatthewsCommented:
sorry gang, didn't refresh before posting
14_eastAuthor Commented:
Hey guys, what the diffreence in the % below (I notice different results):

LIKE <cfqueryparam value="%#form.sr_city#%" cfsqltype="cf_sql_varchar">


LIKE <cfqueryparam value="#form.sr_city#%" cfsqltype="cf_sql_varchar">
The same difference as when you open a DOS box and go:

1) dir document.* (matches document.doc, document.xls but NOT this_document.doc)

2) dir *document.* (matches all three)

% is to SQL like * is for shells. It's just a wildcard for "ANY NUMBER OF ANY CHARACTERS". It does not interpret your idea of what it is to be "LIKE". Forget about the word "LIKE", it's misleading your thoughts into assuming it does something different than wildcarding.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.