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.

Solution 1

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" />

Solution 2

You could lower a.request_id and form.searchbar

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

Solution 3

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.

Solution 4

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

View more solutions