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

About these ads

1 Comment »

  1. SQL> select q’['Hayir dedi Coskan; Onermede buyuk yanlislik var cunku kimileri dogru yolu bulup ailesinden kalan uzucu mirastan kurtulmak icin takimi olan Fe
    nerbahce'yi degistirip buyuk takim Galatasaray'i seciyor.']‘ COSKAN_SAYS from dual;

    COSKAN_SAYS
    ————————————————————————————————————————————————————-
    ——————————————-
    ‘Hayir dedi Coskan; Onermede buyuk yanlislik var cunku kimileri dogru yolu bulup ailesinden kalan uzucu mirastan kurtulmak icin takimi olan Fenerbahce’yi deg
    istirip buyuk takim Galatasaray’i seciyor.’

    Comment by coskan — March 5, 2009 @ 1:50 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 79 other followers

%d bloggers like this: