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

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.

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

Answer 2

You could lower a.request_id and form.searchbar

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

Answer 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.