Tuesday, September 07, 2010

Oracle escaping special characters

In a nutshel

Turn of special meaning of chars
-- The ampersand has no special meaning anymore
SET DEFINE OFF;  
SELECT 'V&D' FROM dual;
Escaping characters in LIKE
-- The meaning of the underscore is escaped
SET ESCAPE ON;
SELECT * FROM maxtable WHERE tablename LIKE 'A\\_%' ESCAPE '\\';



Other Examples

Examples - Supressing special chars
-- Special chars are now treated as normal characters
SET DEFINE OFF;  

-- All characters are in the output
SELECT 'V&D' FROM dual;
SELECT 'V\&D' FROM dual;

-- Escape characters works as expected
SELECT * FROM maxmessages WHERE value LIKE '%\%%' ESCAPE '\';

-- No results...there are no tablenames which start with letter A and a backslash
SELECT * FROM maxtable WHERE tablename LIKE 'A\_%';

-- Runs into an error
SELECT * FROM maxmessages WHERE value LIKE '%\\%%' ESCAPE '\\';
Examples - Escape character
-- Set dash (=default) as an escape character
SET ESCAPE ON;
-- or explicitly set backslash as an escape character
SET ESCAPE \;

-- Escape char works as expected
SELECT 'V\&D' FROM dual;

-- Underscore and Percentage are escaped as expected
SELECT * FROM maxtable WHERE tablename LIKE 'A\\_%' ESCAPE '\\';
SELECT * FROM maxmessages WHERE value LIKE '%\\%%' ESCAPE '\\';

-- Both statemenst have the same result (escape char has no influence)
SELECT * FROM maxtable WHERE tablename LIKE 'A_%';
SELECT * FROM maxtable WHERE tablename LIKE 'A\_%';
-- Special chars are now handled as normal characters
SET DEFINE OFF;  

-- All characters are in the output
SELECT 'V&D' FROM dual;
SELECT 'V\&D' FROM dual;

-- Escape characters works as expected
SELECT * FROM maxmessages WHERE value LIKE '%\%%' ESCAPE '\';

-- No results...there are no tablenames which start with letter A and a backslash
SELECT * FROM maxtable WHERE tablename LIKE 'A\_%';

-- Runs into an error
SELECT * FROM maxmessages WHERE value LIKE '%\\%%' ESCAPE '\\';

-- Variable substitution is performed
SELECT 'V&D' FROM dual;

-- Runs into an error
SELECT * FROM maxmessages WHERE value LIKE '%\%%' ESCAPE '\';


Oracle Documentation

SET DEFINE



SET ESCAPE

1 comment:

  1. There are lots of information about oracle have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get to the next level in oracle. Thanks for sharing this.

    Oracle Training Institutes in Chennai

    ReplyDelete

Comment now »