Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

January 11, 2007

LIKE Escape Characters and Alternative Quoting Mechanism for String Literals

Filed under: Oracle How To — H.Tonguç Yılmaz @ 6:32 am

LIKE Escape Characters

The characters % and _ have special meaning in SQL LIKE clauses. You use % to match zero or more characters and _ to match exactly one character. If you want to interpret these characters literally in strings, then you precede them with a special escape character. For example, if you want to use ampersand (&) as the escape character, then you identify it in the SQL statement as:

SELECT NAME, value FROM v$parameter WHERE NAME LIKE ‘%_io_%’

sessions 49
license_max_sessions 0
license_sessions_warning 0
java_soft_sessionspace_limit 0
java_max_sessionspace_size 0
dbwr_io_slaves 0


dbwr_io_slaves 0
backup_tape_io_slaves FALSE
fast_start_io_target 0

Alternative Quoting Mechanism for String Literals in 10g

SQL statements can use character literals in expressions or conditions. If the literal itself contains a single quotation mark, we need to use two single quotation marks as in ‘Tonguç’s dog’. The second quotation mark inside the character literal acts as an escape character and this additional quotation marks inside a character literal is both cumbersome and error prone.

With 10g we have an alternative. We can use the quote operator, q. This new quote operator allows you to choose your own quotation Mark delimiter and supports both CHAR and NCHAR literals. You can use any convenient delimiter, single or multi byte, or any of the [], {}, (), < > character pairs. This increases readability and usability for applications;

set serveroutput on
str varchar2(128);
str := q'[‘Evet,’ dedi Osman, ‘Galatasaray’lı kaldı mı aranızda? Demiştim, bir gün herkes Fenerbahçe’li olacak..’]’;

‘Evet,’ dedi Osman, ‘Galatasaray’lı kaldı mı aranızda? Demiştim, bir gün herkes Fenerbahçe’li olacak!’

PL/SQL procedure successfully completed

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release

Refences Used :
Oracle® Database JDBC Developer’s Guide and Reference 10g Release 2 (10.2) LIKE Escape Characters
Metalink Note:311971.1 Subject: New Features For Oracle10g PL/SQL 10.x


Blog at WordPress.com.