January 11, 2007

LIKE Escape Characters and Alternative Quoting Mechanism for String Literals

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\_%’ 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;