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
LVL 1
Anandhi1Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Aaron TomoskyConnect With a Mentor SD-WAN SimplifiedCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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 CrossConnect With a Mentor Chief 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
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.