72

As I had written in title, I have SQL query, run on Oracle DB, lets say:

SELECT * FROM TABLE WHERE TABLE.NAME Like 'IgNoReCaSe'

If I would like, that the query would return either "IGNORECASE", "ignorecase" or combinations of them, how can this be done?

zeroDivisible
  • 4,041
  • 8
  • 40
  • 62

9 Answers9

134
Select * from table where upper(table.name) like upper('IgNoreCaSe');

Alternatively, substitute lower for upper.

Hooloovoo
  • 2,181
  • 3
  • 16
  • 21
  • 1
    I'd be careful with the `LIKE`. This can lead to unwanted side-effects, e.g. when the string contains a special char like `%`. Using `upper(table.name) = upper('iGnOrEcASe')` should be safer. – Marteng Aug 18 '21 at 09:42
41

Use ALTER SESSION statements to set comparison to case-insensitive:

alter session set NLS_COMP=LINGUISTIC;
alter session set NLS_SORT=BINARY_CI;

If you're still using version 10gR2, use the below statements. See this FAQ for details.

alter session set NLS_COMP=ANSI;
alter session set NLS_SORT=BINARY_CI;
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
devio
  • 36,858
  • 7
  • 80
  • 143
  • 1
    if zeroDevisible is on 10gR2 or greater, this is much better than function-based indexes, as it will cover all of the 12 fields in the query without the overhead of all those indexes. one potential issue is that the user needs to alter the session every time. – akf Jun 23 '09 at 11:39
  • 3
    I'm not sure what overhead you're talking about. If you create a "normal" index, then change the NLS_COMP and NLS_SORT, Oracle won't be able to use the index any longer to find the data in question. So you would end up creating NLS setting-specific indexes for whatever columns would be appropriate. It's not obvious to me how that generates any more or less overhead than function-based indexes (FBI). Obviously, if you want all queries to be case insensitive, there is no need to maintain an index on a column and a FBI on UPPER(column) (or to maintain indexes for different NLS settings) – Justin Cave Jun 23 '09 at 14:21
  • 1
    @devio Dosent work for me in version : 12 c please update your answer – Nishanth Shaan Jul 11 '17 at 15:25
29

You can use either lower or upper function on both sides of the where condition

joe
  • 34,529
  • 29
  • 100
  • 137
  • Thank You for such an fast answer. I am wondering, because my query selects about 12 fields from really huge table, so I would need about 20 uses of upper function - wouldn't it be a performance hit? – zeroDivisible Jun 23 '09 at 10:57
  • If you know the exact words you want to check against, you could use an IN statement (SELECT * FROM TABLE WHERE UPPER(NAME) IN (UPPER('Name1'), UPPER('Name2')); or if the names all start the same you could do ths with a wildcard (SELECT * FROM TABLE WHERE UPPER(NAME) LIKE UPPER('Search%');) – Hooloovoo Jun 23 '09 at 11:14
  • 6
    The use of functions like upper is never a performance hit if you're talking about the time it takes Oracle to perform the operation - the time it takes the cpu to do the conversion is trivial compared to the time it takes to load the data pages from the cache, never mind getting it from the disk. However, using lower or upper will prevent your query from using any indexes on those columns, unless you created indexes that used those functions. But LIKE could possibly prevent index use as well, particularly if the first character in the expression is a wildcard. – Steve Broberg Jun 23 '09 at 14:21
17

You could also use Regular Expressions:

SELECT * FROM TABLE WHERE REGEXP_LIKE (TABLE.NAME,'IgNoReCaSe','i');
AliSh
  • 10,085
  • 5
  • 44
  • 76
kMAP
  • 171
  • 2
8

You can use the upper() function in your query, and to increase performance you can use a function-base index

 CREATE INDEX upper_index_name ON table(upper(name))
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
akf
  • 38,619
  • 8
  • 86
  • 96
5

You can convert both values to upper or lowercase using the upper or lower functions:

Select * from table where upper(table.name) like upper('IgNoreCaSe')

or

Select * from table where lower(table.name) like lower('IgNoreCaSe');
bryansoftdev
  • 69
  • 10
user3666177
  • 61
  • 1
  • 1
  • 1
    This is probably the right answer but note that it might break any indexing you have on your strings. If you need indexing you might want to add a new column where you always insert the case-normalized value. – wjl May 22 '14 at 17:47
  • Why the `or`? Is it possible for uppercase to match but lowercase not match? – Jon Heller May 23 '14 at 00:08
  • 5
    I think @user3666177 posted the `or` to demonstrate both options. I don't think it's possible for one to match but not the other. – wjl May 28 '14 at 17:36
5

In version 12.2 and above, the simplest way to make the query case insensitive is this:

SELECT * FROM TABLE WHERE TABLE.NAME COLLATE BINARY_CI Like 'IgNoReCaSe'
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
Giorgio Testa
  • 51
  • 1
  • 1
4

...also do the conversion to upper or lower outside of the query:

tableName:= UPPER(someValue || '%');

...

Select * from table where upper(table.name) like tableName
ozczecho
  • 8,649
  • 8
  • 36
  • 42
0

Also don't forget the obvious, does the data in the tables need to have case? You could only insert rows already in lower case (or convert the existing DB rows to lower case) and be done with it right from the start.

Gandalf
  • 9,648
  • 8
  • 53
  • 88
  • this is an issue if you have proper nouns. Particularly if you have both proper and non-proper nouns in the same field. – kralco626 Jan 19 '11 at 17:11