Escape special character (Still some people don't know 10g mechanism!):

To escape single quote in a string we used to add another single quote before -or after :)- the original single quote to consider those two single quote as one single quote within the string and not a string terminator, as the following:

SELECT 'I don''t like my brother''s car'

  FROM DUAL

But do we have to do that for every single quote? Well we had to do it, but not anymore. In oracle 10g a new feature is introduced to make it easier to Oracle developers. The previous query would be:

SELECT q'!I don't like my brother's car!'

  FROM DUAL

You may notice the exclamation mark "!" which is not the only character you can use, you can use "[" and "]" as start and end respectively, or any character that is found in the original string with a succeeding single quote and it's better to choose a special character like "[ and ]", "{ and }", "!" ....

All of these queries are right:

SELECT q'[I don't like my brother's car]'

  FROM DUAL

 

SELECT q'{I don't like my brother's car}'

  FROM DUAL

Even:

SELECT q'ZI don't like my brother's carZ'

  FROM DUAL




Saad Nayef,