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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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..
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..
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

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.

%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%  

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.