Case insensitive searching in Oracle

Question

The default behaviour of LIKE and the other comparison operators    etc is case-sensitive   Is it possible make them case-insensitive

User · Answer

select user name from my table where nlssort user name   NLS SORT   Latin CI     nlssort   AbC     NLS SORT   Latin CI

User · Answer

you can do something like that   where regexp like name   string     i

User · Answer

From Oracle 12c R2 you could use COLLATE operator      The COLLATE operator determines the collation for an expression  This operator enables you to override the collation that the database would have derived for the expression using standard collation derivation rules        The COLLATE operator takes one argument  collation name  for which you can specify a named collation or pseudo-collation  If the collation name contains a space  then you must enclose the name in double quotation marks    Demo   CREATE TABLE tab1 i INT PRIMARY KEY  name VARCHAR2 100     INSERT INTO tab1 i  name  VALUES  1   John    INSERT INTO tab1 i  name  VALUES  2   Joe    INSERT INTO tab1 i  name  VALUES  3   Billy     --                                                                        -- SELECT   csv     FROM tab1 WHERE name    jOHN    -- no rows selected  SELECT   csv     FROM tab1 WHERE name COLLATE BINARY CI    jOHN        I   NAME  1  John      SELECT   csv     FROM tab1  WHERE name LIKE  j    -- no rows selected  SELECT   csv     FROM tab1  WHERE name COLLATE BINARY CI LIKE  j        I   NAME  1  John  2  Joe       db lt  fiddle demo

User · Answer

Since 10gR2  Oracle allows to fine-tune the behaviour of string comparisons by setting the NLS COMP and NLS SORT session parameters   SQL gt  SET HEADING OFF SQL gt  SELECT     2  FROM NLS SESSION PARAMETERS   3  WHERE PARAMETER IN   NLS COMP    NLS SORT     NLS SORT BINARY  NLS COMP BINARY   SQL gt  SQL gt  SELECT CASE WHEN  abc   ABC  THEN 1 ELSE 0 END AS GOT MATCH   2  FROM DUAL            0  SQL gt  SQL gt  ALTER SESSION SET NLS COMP LINGUISTIC   Session altered   SQL gt  ALTER SESSION SET NLS SORT BINARY CI   Session altered   SQL gt  SQL gt  SELECT     2  FROM NLS SESSION PARAMETERS   3  WHERE PARAMETER IN   NLS COMP    NLS SORT     NLS SORT BINARY CI  NLS COMP LINGUISTIC   SQL gt  SQL gt  SELECT CASE WHEN  abc   ABC  THEN 1 ELSE 0 END AS GOT MATCH   2  FROM DUAL            1   You can also create case insensitive indexes   create index    nlsci1 gen person on    MY PERSON     NLSSORT        PERSON LAST NAME   NLS SORT BINARY CI            This information was taken from Oracle case insensitive searches  The article mentions REGEXP LIKE but it seems to work with good old   as well     In versions older than 10gR2 it can t really be done and the usual approach  if you don t need accent-insensitive search  is to just UPPER   both the column and the search expression

User · Answer

There are 3 main ways to perform a case-insensitive search in Oracle without using full-text indexes   Ultimately what method you choose is dependent on your individual circumstances  the main thing to remember is that to improve performance you must index correctly for case-insensitive searching   1  Case your column and your string identically   You can force all your data to be the same case by using UPPER   or LOWER     select   from my table where upper column 1    upper  my string      or  select   from my table where lower column 1    lower  my string      If column 1 is not indexed on upper column 1  or lower column 1   as appropriate  this may force a full table scan  In order to avoid this you can create a function-based index   create index my index on my table   lower column 1       If you re using LIKE then you have to concatenate a   around the string you re searching for   select   from my table where lower column 1  LIKE lower  my string             This SQL Fiddle demonstrates what happens in all these queries  Note the Explain Plans  which indicate when an index is being used and when it isn t   2  Use regular expressions   From Oracle 10g onwards REGEXP LIKE   is available  You can specify the  match parameter   i   in order to perform case-insensitive searching   In order to use this as an equality operator you must specify the start and end of the string  which is denoted by the carat and the dollar sign   select   from my table where regexp like column 1    my string     i      In order to perform the equivalent of LIKE  these can be removed   select   from my table where regexp like column 1   my string    i      Be careful with this as your string may contain characters that will be interpreted differently by the regular expression engine   This SQL Fiddle shows you the same example output except using REGEXP LIKE     3  Change it at the session level   The NLS SORT parameter governs the collation sequence for ordering and the various comparison operators  including   and LIKE  You can specify a binary  case-insensitive  sort by altering the session  This will mean that every query performed in that session will perform case-insensitive parameters   alter session set nls sort BINARY CI   There s plenty of additional information around linguistic sorting and string searching if you want to specify a different language  or do an accent-insensitive search using  BINARY AI   You will also need to change the NLS COMP parameter  to quote      The exact operators and query clauses that obey the NLS SORT parameter   depend on the value of the NLS COMP parameter  If an operator or   clause does not obey the NLS SORT value  as determined by NLS COMP    the collation used is BINARY    The default value of NLS COMP is BINARY  but  LINGUISTIC specifies that Oracle should pay attention to the value of NLS SORT      Comparisons for all SQL operations in the WHERE clause and in PL SQL   blocks should use the linguistic sort specified in the NLS SORT   parameter  To improve the performance  you can also define a   linguistic index on the column for which you want linguistic   comparisons    So  once again  you need to alter the session  alter session set nls comp LINGUISTIC   As noted in the documentation you may want to create a linguistic index to improve performance  create index my linguistc index on my table      NLSSORT column 1   NLS SORT   BINARY CI

User · Answer

maybe you can try using   SELECT user name FROM user master WHERE upper user name  LIKE   ME

[sql] Case insensitive searching in Oracle

Examples related to sql

Examples related to oracle

Examples related to case-sensitive

Examples related to case-insensitive

Examples related to sql-like