Case- and Accent-Insensitive Searches

Page 172

In the next example, we want to find all character values with either the letter b or c in them, and then we want to only print out characters in those strings that have b, c, or d values:

 SQL> SELECT char_value,   2 REGEXP_SUBSTR(char_value,'([b-d]+/?) {0,5}/?')   3 FROM test_expressions   4 WHERE REGEXP_INSTR(char_value,'[b-c]',1,2) > 0; CHAR_VALUE                    REGEXP_SUBSTR(CHAR_VALUE,'([B- ----------------------------- ------------------------------ abcd                          bcd abb                           bb

Oracle Database 10g now allows for case- and accent-insensitive sorting. This is supported through the use of the nls_sort parameter, affixing the parameters _ai for accent insensitive and _ci for case insensitive to the NLS language you are using.

The nls_sort parameter affects a number of SQL functions and operations, including the following:

where	in/not in
order by	between
start with	case-when
having

The SQL functions that compare based on byte order rather than character (operations such as like, trim, and instr) are not affected by the nls_sort settings. Also, you can

Jonathan Says...

In case you can't wait for Oracle Database 10g to do your regular expression search-and-replace tasks, you might want to look at the owa_pattern package, which has been around for several years. See $ORACLE_HOME/rdbms/admin/pubpat.sql for details.

It's just a PL/SQL package (surely a candidate for native compilation), so it's not fast. On the other hand, I don't think anyone will be depending on regular expression handling to find data quickly even in Oracle Database 10g; it is more likely to be the final test applied to a small number of rows, or an occasional text-processing trawl through an individual LOB.