RE: How does one escape special characters

SELECT * FROM emp WHERE name LIKE '% O''REAR' ; -- to find rows with name like "... O'REAR" http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_ elements3a.htm#42620
<http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/sql _elements3a.htm#42620>

Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 Literals: Text Literals
...
' ' are two single quotation marks that begin and end text literals. To
represent one single quotation mark within a literal, enter two single quotation marks.
...
Here are some valid text literals:
'Hello'
'ORACLE.dbs'
'Jackie''s raincoat'
'09-MAR-98'

N'nchar literal'

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/cond itions10a.htm#1041580
<http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/con ditions10a.htm#1041580>
LIKE Conditions

x [NOT] LIKE y [ESCAPE 'z']

TRUE if x does [not] match the pattern y. Within y, the character "%" matches any string of zero or more characters except null. The character "_" matches any single character. Any character can follow ESCAPE except percent (%) and underbar (_). A wildcard character is treated as a literal if preceded by the character designated as the escape character. SELECT last_name

    FROM employees
    WHERE last_name LIKE '%A\_B%' ESCAPE '\';

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Roger Xu Sent: lundi, 24. janvier 2005 15:11
To: Oracle-L_at_Freelists. Org (E-mail)
Subject: How does one escape special characters

Hello list,

I would use the following SQL to search for someone whose last name is O'REAR in sqlplus.

SELECT * FROM emp WHERE name LIKE '% O/'REAR' ESCAPE '/';

How do I do it if I want to build the SQL first in a variable for a PL/SQL statement?

cmd:='SELECT' || l_column || 'FROM emp WHERE name LIKE '% O/'REAR' ESCAPE '/';
--This gives me an error.

--
http://www.freelists.org/webpage/oracle-l