Entry

Is the LIKE statement case sensitive?

Does LIKE '%ABC%' match ABC, abc, AbC and so on?

May 16th, 2000 22:06
Nathan Wallace, Graham Barr

SELECT * FROM table WHERE column LIKE "%ABC%"

I want it to return for ABC abc AbC and so on...how do i do that?

It already does if "column" is defined as a normal char or varchar
column.  If "column" is a binary column, then the search is case
sensitive. Don't know how to choose which to use at search time, though. 

I've found binary searches to be of the order of 40% faster than case 
insensitive searches for %% searching on reasonably big tables. YMMV.
:-)

Also

 SELECT * FROM table WHERE UPPER(column) LIKE "%ABC%"

can be used for case in-sensitive search in binary columns, but it has a
performance hit.