Geek Answers Handbook

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

I use to write a query of choice, and the value for an expression of type dynamic 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 only returns results where the case of each character in the line # form.searchbar # is matched.

Please suggest a workaround for this to become case insensitive.

+5
source share
4 answers

, , Oracle, . , , , , , . - , Oracle:

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

queryparam, , :

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

, Oracle:

, .

, , SQL-. .

+2

a.request_id form.searchbar

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

You can force all caps, as suggested by Snipe656. You can also use the regexp_instrcase-insensitive search function . For example, to search for a table EMPfor each row that ENAMEcontains the string 'in' in case-insensitive mode

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 will probably be something like

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

More articles:


All Articles