Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 503
  • Last Modified:

Sql query using 'like' and ignore case

I would like to obtain results using the 'like' word but I want it to ignore the case in the results.

For example
select * from table1 where column1 like 'aBcD'
should return all the rows where column1 is ABCD, abcd, etc.

thanks,
Anu
0
Anandhi1
Asked:
Anandhi1
2 Solutions
 
Aaron TomoskyTechnology ConsultantCommented:
If you are using the default Charset SQL_Latin1_General_CP1_CI_AS the ci stands for case insensitive.
0
 
Anandhi1Author Commented:
Currently my query
select * from table1 where column1 like 'ABCD%' returns only the 'ABCD' not 'abcd' .

Is it possible to do in SQL?

0
 
LIONKINGCommented:
You can upper everything and give it a try, something like:

select * from table1 where UPPER(column1) like UPPER('ABCD%')
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Anandhi1Author Commented:
That worked but the query took a long time to execute. It took a minute instead of a second.

Do we have any other solution?

thanks,
Anu
0
 
Kevin CrossChief Technology OfficerCommented:
That is because you wrapped the column1 with a function. Try it with COLLATE. Using aaron's Charset:

SELECT *
FROM table1 
WHERE column1 COLLATE SQL_Latin1_General_CP1_CI_AS LIKE 'ABCD%';

Open in new window

0
 
sath350163Commented:
If column1 has index on it, doing UPPER(column1) will prevent that index from being used.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now