Like SQL Statement

I am doing a query where I am lookin for
the word Century Park in a string in a field.  This
string was input freehand so there could be
different variations. It could have the string
"Century Park" or "century park" or "Century park"
or "century Park" or other variations.

Is there a way to capture all of these different
variations without having to write "Like" statements
for all of these different variations?
donnie91910Asked:
Who is Participating?
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The one using LOWER will work, as said already. You might want to use it (LOWER) twice:
SELECT * FROM YourTable
WHERE LOWER(YourField) LIKE LOWER('%century park%')

Open in new window

0
 
Saurabh Singh TeotiaCommented:
You can do something like this...

SELECT * FROM parks
WHERE UCASE(Park) LIKE '%CENTURY PARK%'; 

Open in new window


Saurabh...
0
 
dsackerContract ERP Admin/ConsultantCommented:
Try:

SELECT * FROM YourTable
WHERE CAPS(YourField) LIKE '%CENTURY PARK%'

The other way works, too:

SELECT * FROM YourTable
WHERE LC(YourField) LIKE '%century park%'

If your database doesn't like those, try:

SELECT * FROM YourTable
WHERE LOWER(YourField) LIKE '%century park%'
0
Upgrade your Question Security!

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

 
Kyle AbrahamsSenior .Net DeveloperCommented:
You can change the collation on the fly for a query:

SELECT Col_Name
FROM myTable 
WHERE COL_NAME COLLATE SQL_Latin1_General_CP1_CI_AS  LIKE '%CENTURY PARK%'

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Kyle, only if it is MSSQL ;-).

donnie91910, the correct answer depends on the DBMS you are using this in.
0
 
donnie91910Author Commented:
so there isn't a catch all for these different variations?

"Century Park" or "century park" or "Century park"
or "century Park"
0
 
donnie91910Author Commented:
the data is coming from an Oracle database.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The last suggestion in http:#a40715468 should work then to catch all matches, case independent. But no phonetic alternatives, variations with spaces etc.
0
 
dsackerContract ERP Admin/ConsultantCommented:
Exactly.

I gave a few examples, not sure which type database. LOWER will work on both SQL Server and Oracle. LC and CAPS work on Progress databases. LCASE also works on a number of databases.
0
 
donnie91910Author Commented:
what I am looking for is "one" statement that can cover any kind of case in the string.
0
 
donnie91910Author Commented:
the only reason I need this is because it is a free hand field and who knows how the user spelled it.
0
 
donnie91910Author Commented:
thanks all :
SELECT * FROM YourTable
WHERE LOWER(YourField) LIKE LOWER('%century park%')

worked.
0
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.