LIKE Escape Characters and Alternative Quoting Mechanism for String Literals

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
fileio_network_adapters
filesystemio_options
dbwr_io_slaves 0
…

SELECT NAME, VALUE FROM v$parameter WHERE NAME LIKE ‘%\_io\_%’ ESCAPE ‘\’

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
declare
str varchar2(128);
begin
str := q'[‘Evet,’ dedi Osman, ‘Galatasaray’lı kaldı mı aranızda? Demiştim, bir gün herkes Fenerbahçe’li olacak..’]’;
dbms_output.put_line(str);
end;
/

‘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 10.2.0.1.0

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

Advertisement