Rectangle 27 228

1. Case your column and your string identically.

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.

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');

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.

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.

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().

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.

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.

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'));

"create a function-based index" Amazing what a difference this can make

Really nice answer @Ben covers all the ways i could find in multiple locations.

+1. Also in the 4th query from top, I guess to intended to write "LIKE" instead of =

select * from my_table where lower(column_1) LIKE lower('my_string%');

One reason would be if your query is paramerterized (likely in most situations) then your calling code doesn't need to always concatenate a % on the end @lopezvit.

If there are some characters that will mess-up the result of regexp_like, is there a way to escape such strings? Giving an example, if the string has $, the output will be not as what we expect. //cc @Ben and others please do share.

` is the escape character @bozzmob. There should be no difference in output if the string the regular expression is operating on contains a $, this may only cause you problems if you need a $ literal in your regular expression. If you've got a specific issue I'd ask another question if this comment/answer hasn't helped.

sql - Case insensitive searching in Oracle - Stack Overflow

sql oracle case-sensitive case-insensitive sql-like
Rectangle 27 223

1. Case your column and your string identically.

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.

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');

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.

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().

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.

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.

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'));

Really nice answer @Ben covers all the ways i could find in multiple locations.

+1. Also in the 4th query from top, I guess to intended to write "LIKE" instead of =

"create a function-based index" Amazing what a difference this can make

One reason would be if your query is paramerterized (likely in most situations) then your calling code doesn't need to always concatenate a % on the end @lopezvit.

` is the escape character @bozzmob. There should be no difference in output if the string the regular expression is operating on contains a $, this may only cause you problems if you need a $ literal in your regular expression. If you've got a specific issue I'd ask another question if this comment/answer hasn't helped.

sql - Case insensitive searching in Oracle - Stack Overflow

sql oracle case-sensitive case-insensitive sql-like
Rectangle 27 1506

You need to use the \c escape sequence. So:

/\ccopyright

To do the inverse (case sensitive matching), use \C instead.

Also, \c can appear anywhere in the pattern, so if you type a pattern and then decide you wanted a case-insensitive search, just add a \c at the end.

set ignorecase
\C

There's also set smartcase which will automatically switch to a case-sensitive search if you use any capital letters.

Just want to add to Zaz's comment. set smartcase applies only when set ignorecase is already active. I was stumped on this for a while. See Vim Tips.

@coderMe, It's the capitalization, \c versus \C

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

How to do case insensitive search in Vim - Stack Overflow

search vim case-insensitive
Rectangle 27 1503

You need to use the \c escape sequence. So:

/\ccopyright

To do the inverse (case sensitive matching), use \C instead.

Also, \c can appear anywhere in the pattern, so if you type a pattern and then decide you wanted a case-insensitive search, just add a \c at the end.

set ignorecase
\C

There's also set smartcase which will automatically switch to a case-sensitive search if you use any capital letters.

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

Just want to add to Zaz's comment. set smartcase applies only when set ignorecase is already active. I was stumped on this for a while. See Vim Tips.

@coderMe, It's the capitalization, \c versus \C

How to do case insensitive search in Vim - Stack Overflow

search vim case-insensitive
Rectangle 27 1497

You need to use the \c escape sequence. So:

/\ccopyright

To do the inverse (case sensitive matching), use \C instead.

Also, \c can appear anywhere in the pattern, so if you type a pattern and then decide you wanted a case-insensitive search, just add a \c at the end.

set ignorecase
\C

There's also set smartcase which will automatically switch to a case-sensitive search if you use any capital letters.

Just want to add to Zaz's comment. set smartcase applies only when set ignorecase is already active. I was stumped on this for a while. See Vim Tips.

@coderMe, It's the capitalization, \c versus \C

How to do case insensitive search in Vim - Stack Overflow

search vim case-insensitive
Rectangle 27 523

As well as the suggestions for \c and ignorecase, I find the smartcase very useful. If you search for something containing uppercase characters, it will do a case sensitive search; if you search for something purely lowercase, it will do a case insensitive search. You can use \c and \C to override this:

:set ignorecase
:set smartcase
/copyright      " Case insensitive
/Copyright      " Case sensitive
/copyright\C    " Case sensitive
/Copyright\c    " Case insensitive
:help /\c
:help /\C
:help 'smartcase'

The problem with ignorecase is that it affects substitutions as well as searches. I find that it makes sense to have (smart) case-insensitive searches but case-sensitive substitutions by default. But there's no way to do that that I know.

smartcase
set ignorecase

I believe you could just use a \C in your search expression for substitutions, like this: :%s/lowercasesearch\C/replaceString/g. This doesn't create the default functionality you desire, but it does allow you to force case-sensitivity for replacements while still benefiting from smartcase when searching.

You can also set the I flag on a substitution to force the pattern to be case-sensitive. Like :%s/lowercasesearch/replaceString/gI.

After	"*" and "#" you can make 'smartcase' used by doing a "/" command, 	recalling the search pattern from history and hitting <Enter>.

How to do case insensitive search in Vim - Stack Overflow

search vim case-insensitive
Rectangle 27 522

As well as the suggestions for \c and ignorecase, I find the smartcase very useful. If you search for something containing uppercase characters, it will do a case sensitive search; if you search for something purely lowercase, it will do a case insensitive search. You can use \c and \C to override this:

:set ignorecase
:set smartcase
/copyright      " Case insensitive
/Copyright      " Case sensitive
/copyright\C    " Case sensitive
/Copyright\c    " Case insensitive
:help /\c
:help /\C
:help 'smartcase'

The problem with ignorecase is that it affects substitutions as well as searches. I find that it makes sense to have (smart) case-insensitive searches but case-sensitive substitutions by default. But there's no way to do that that I know.

smartcase
set ignorecase

I believe you could just use a \C in your search expression for substitutions, like this: :%s/lowercasesearch\C/replaceString/g. This doesn't create the default functionality you desire, but it does allow you to force case-sensitivity for replacements while still benefiting from smartcase when searching.

You can also set the I flag on a substitution to force the pattern to be case-sensitive. Like :%s/lowercasesearch/replaceString/gI.

After	"*" and "#" you can make 'smartcase' used by doing a "/" command, 	recalling the search pattern from history and hitting <Enter>.

How to do case insensitive search in Vim - Stack Overflow

search vim case-insensitive
Rectangle 27 522

As well as the suggestions for \c and ignorecase, I find the smartcase very useful. If you search for something containing uppercase characters, it will do a case sensitive search; if you search for something purely lowercase, it will do a case insensitive search. You can use \c and \C to override this:

:set smartcase
/copyright      " Case insensitive
/Copyright      " Case sensitive
/copyright\C    " Case sensitive
/Copyright\c    " Case insensitive
:set ignorecase
:set smartcase
/copyright      " Case insensitive
/Copyright      " Case sensitive
/copyright\C    " Case sensitive
/Copyright\c    " Case insensitive
:help /\c
:help /\C
:help 'smartcase'

The problem with ignorecase is that it affects substitutions as well as searches. I find that it makes sense to have (smart) case-insensitive searches but case-sensitive substitutions by default. But there's no way to do that that I know.

smartcase
set ignorecase

I believe you could just use a \C in your search expression for substitutions, like this: :%s/lowercasesearch\C/replaceString/g. This doesn't create the default functionality you desire, but it does allow you to force case-sensitivity for replacements while still benefiting from smartcase when searching.

You can also set the I flag on a substitution to force the pattern to be case-sensitive. Like :%s/lowercasesearch/replaceString/gI.

After	"*" and "#" you can make 'smartcase' used by doing a "/" command, 	recalling the search pattern from history and hitting <Enter>.

How to do case insensitive search in Vim - Stack Overflow

search vim case-insensitive
Rectangle 27 64

Since 10gR2, Oracle allows to fine-tune the behaviour of string comparisons by setting the NLS_COMP and NLS_SORT session parameters:

SQL> SET HEADING OFF
SQL> SELECT *
  2  FROM NLS_SESSION_PARAMETERS
  3  WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');

NLS_SORT
BINARY

NLS_COMP
BINARY


SQL>
SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH
  2  FROM DUAL;

         0

SQL>
SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;

Session altered.

SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;

Session altered.

SQL>
SQL> SELECT *
  2  FROM NLS_SESSION_PARAMETERS
  3  WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');

NLS_SORT
BINARY_CI

NLS_COMP
LINGUISTIC


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

This works well, but it makes the UPDATES using the LIKE / = operators very slow...... :(

@SaqibAli Arbitrary LIKE expressions (e.g. WHERE foo LIKE '%abc%') are already slow enough if they can't be indexed, I don't think it's specifically related to case sensitiveness.

You can also set these outside of SQLPLUS, like in the shell environment. For example in a Perl script using DBD::Oracle, you can write $ENV{NLS_SORT} = 'BINARY_CI'; $ENV{NLS_COMP} = 'LINGUISTIC'; before calling ` DBI->connect`.

hey does the ALTER SESSION only alter your local instance of the correction and does it mean like your current session i.e. if i close and reopen it would have reset. Is there a way that i can see what the current values are so that if its persisted everywhere i can change back to original settings...

sql - Case insensitive searching in Oracle - Stack Overflow

sql oracle case-sensitive case-insensitive sql-like
Rectangle 27 64

Since 10gR2, Oracle allows to fine-tune the behaviour of string comparisons by setting the NLS_COMP and NLS_SORT session parameters:

SQL> SET HEADING OFF
SQL> SELECT *
  2  FROM NLS_SESSION_PARAMETERS
  3  WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');

NLS_SORT
BINARY

NLS_COMP
BINARY


SQL>
SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH
  2  FROM DUAL;

         0

SQL>
SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;

Session altered.

SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;

Session altered.

SQL>
SQL> SELECT *
  2  FROM NLS_SESSION_PARAMETERS
  3  WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');

NLS_SORT
BINARY_CI

NLS_COMP
LINGUISTIC


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

This works well, but it makes the UPDATES using the LIKE / = operators very slow...... :(

@SaqibAli Arbitrary LIKE expressions (e.g. WHERE foo LIKE '%abc%') are already slow enough if they can't be indexed, I don't think it's specifically related to case sensitiveness.

You can also set these outside of SQLPLUS, like in the shell environment. For example in a Perl script using DBD::Oracle, you can write $ENV{NLS_SORT} = 'BINARY_CI'; $ENV{NLS_COMP} = 'LINGUISTIC'; before calling ` DBI->connect`.

hey does the ALTER SESSION only alter your local instance of the correction and does it mean like your current session i.e. if i close and reopen it would have reset. Is there a way that i can see what the current values are so that if its persisted everywhere i can change back to original settings...

sql - Case insensitive searching in Oracle - Stack Overflow

sql oracle case-sensitive case-insensitive sql-like
Rectangle 27 223

You can set the ic option in Vim before the search:

:set ic
:set noic
ic
ignorecase

How to do case insensitive search in Vim - Stack Overflow

search vim case-insensitive
Rectangle 27 223

You can set the ic option in Vim before the search:

:set ic
:set noic
ic
ignorecase

How to do case insensitive search in Vim - Stack Overflow

search vim case-insensitive
Rectangle 27 223

You can set the ic option in Vim before the search:

:set ic
:set noic
ic
ignorecase

How to do case insensitive search in Vim - Stack Overflow

search vim case-insensitive
Rectangle 27 40

maybe you can try using

SELECT user_name
FROM user_master
WHERE upper(user_name) LIKE '%ME%'

it works when input parameter is whole upper-case ,and if lower or mixed it doesn't

WHERE upper(user_name) LIKE UPPER('%ME%')

@sergionni you must upper case the search term as well!

@sergionni, well then why don't you use UPPER on the input parameter too?

@V4Vendetta using the upper function you lose the index, do you have any idea how to make search using the index?

sql - Case insensitive searching in Oracle - Stack Overflow

sql oracle case-sensitive case-insensitive sql-like
Rectangle 27 39

maybe you can try using

SELECT user_name
FROM user_master
WHERE upper(user_name) LIKE '%ME%'

it works when input parameter is whole upper-case ,and if lower or mixed it doesn't

WHERE upper(user_name) LIKE UPPER('%ME%')

@sergionni you must upper case the search term as well!

@sergionni, well then why don't you use UPPER on the input parameter too?

@V4Vendetta using the upper function you lose the index, do you have any idea how to make search using the index?

sql - Case insensitive searching in Oracle - Stack Overflow

sql oracle case-sensitive case-insensitive sql-like
Rectangle 27 38

nmap <F9> :set ignorecase! ignorecase?
:set ignorecase!

Yes, but ignorecase? shows you the current state of the flag. (in the command line)

How to do case insensitive search in Vim - Stack Overflow

search vim case-insensitive
Rectangle 27 38

nmap <F9> :set ignorecase! ignorecase?
:set ignorecase!

Yes, but ignorecase? shows you the current state of the flag. (in the command line)

How to do case insensitive search in Vim - Stack Overflow

search vim case-insensitive