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?

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

x
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.

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
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
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Qlemo"Batchelor", 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
Qlemo"Batchelor", 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
Qlemo"Batchelor", 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

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
donnie91910Author Commented:
thanks all :
SELECT * FROM YourTable
WHERE LOWER(YourField) LIKE LOWER('%century park%')

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

From novice to tech pro — start learning today.