How does one escape special characters 2005-01-24 - By Jacques Kilchoer
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@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Roger Xu
Sent: lundi, 24. janvier 2005 15:11
To: Oracle-L@(protected) 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
|
|
