Is it possible to perform a case insensitive search in LIKE statement in SQL?

Question

I am using to write a select query and the value for like statement is dynamic.

  AND       e.rank_request_id = a.request_id
  AND       f.priority_request_id = a.request_id
  AND       b.status_type_id = c.status_id
  AND   b.status_request_id = a.request_id
  AND   a.request_id LIKE '%#form.searchbar#%'

But this returns results only where Case of each character in the string #form.searchbar# is matched.

Please suggest a workaround for this so that it becomes case-insensitive.

Which DBMS? Forcing the casing of both sides of your test is often what is done, regardless of system/language. — Tim Lehner, Mar 29 '12 at 19:00

Snipe656 · Accepted Answer · 2012-03-29T19:06:13.563

18

I do not know what database you are using but if this were for Oracle then you could just force the case of both things. This though comes at a cost for execution times since it does it for all values in that column but you'd only see the cost if you have a lot of data and could work around that with a function based index. So something like this, again for Oracle:

AND UPPER(a.request_id) LIKE '%#UCase(Form.Searchbar)#%'

But I would suggest you use a queryparam since appears to come from a user inputted box, so:

AND UPPER(a.request_id) LIKE <cfqueryparam value="%#UCase(Form.Searchbar)#%" cfsqltype="cf_sql_varchar" />

edited Mar 29 '12 at 19:06

answered Mar 29 '12 at 18:57

Snipe656

845
7
15

If you use upper in a column, the database will not use an index if defined in that column, son in a big table this will run very slow. You can define an index with the upper function and leave the query like the example and then the database will use the index. Always review the execution plan for the decision made by the database to use or not your index. – Juan Oct 06 '14 at 01:22
True, if it is a large table running any function on the column like my example will be slow. Table size and slowness dependent upon resources/setup of course. I just wanted to add to the suggestion in case the original poster runs into this or someone else, do a search on Function Based Indexing. – Snipe656 Jul 29 '19 at 17:13

score 3 · Answer 2 · answered Mar 29 '12 at 18:57

3

You could lower a.request_id and form.searchbar

AND lower(a.request_id) LIKE '%#form.searchbar#%'

answered Mar 29 '12 at 18:57

Soader03

351
4
20

It fails if '%#form.searchbar#%' is something like Test or tEst – aman.kejriwal Mar 29 '12 at 19:02
Yes, just figured out. Forgot to tell to make form.searchbar lower too ;) – Soader03 Mar 29 '12 at 19:10

score 2 · Answer 3 · edited May 23 '17 at 12:33

2

There are already many questions about case-insensitive where clauses in Oracle:

For example, here.

Also, this query looks like it may be vulnerable to SQL injection attacks. More info here.

edited May 23 '17 at 12:33

Community

1
1

answered Mar 29 '12 at 19:05

Tim Lehner

14,813
4
59
76

This question differs a bit as it's related to ColdFusion also. First of it's kind on StackOverflow – aman.kejriwal Mar 29 '12 at 19:08

score 1 · Answer 4 · answered Mar 29 '12 at 19:23

You can force everything to uppercase as Snipe656 suggests. You can also use the regexp_instr function to do a case-insensitive search. For example, to search the EMP table for every row where ENAME contains the string 'in' in a case-insensitive fashion

SQL> ed
Wrote file afiedt.buf

  1  select ename, empno
  2    from emp
  3*  where regexp_instr( ename, 'in', 1, 1, 1, 'i' ) > 0
SQL> /

ENAME           EMPNO
---------- ----------
MARTIN           7654
KING             7839

In your case, it would probably be something like

AND   regexp_instr( a.request_id, '#form.searchbar#', 1, 1, 1, 'i' ) > 0

Is it possible to perform a case insensitive search in LIKE statement in SQL?

4 Answers4