View

Echappement des caractères spéciaux dans les requêtes SQL

  1. Escape quotes
  2. USE two quotes FOR every one displayed. Examples:
  3. SQL> SELECT ‘Frank’‘s Oracle site’ AS text FROM DUAL;
  4.  TEXT
  5.  ——————–
  6.  Franks‘s Oracle site
  7. SQL> SELECT ‘A quoted word.‘ AS text FROM DUAL;
  8. TEXT
  9. —————-
  10. A ‘quoted‘ word.
  11. SQL> SELECT ‘A double quoted word.‘ AS text FROM DUAL;
  12. TEXT
  13. ————————-
  14. A ‘‘double quoted’‘ word.
  15. Escape wildcard characters
  16. The LIKE keyword allows for string searches. The ‘_‘ wild card character is used to match exactly one character, while ‘%‘ is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Examples:
  17. SELECT name FROM emp
  18. WHERE id LIKE ‘%/_%‘ ESCAPE ‘/‘;
  19. SELECT name FROM emp
  20. WHERE id LIKE ‘%\%%‘ ESCAPE ‘\‘;
  21. Escape ampersand (&) characters in SQL*Plus
  22. When using SQL*Plus, the DEFINE setting can be changed to allow &’s (ampersands) TO be used IN text:
  23. SET DEFINE ~
  24. SELECT ‘Lorel & Hardy’ FROM dual;
  25. Other methods:
  26. Define an escape character:
  27. SET ESCAPE \’
  28. SELECT ‘\&abc‘ FROM dual;
  29. Don’t scan FOR substitution VARIABLES:
  30. SET SCAN OFF
  31. SELECT ‘&ABC’ x FROM dual;
  32. USE the 10g Quoting mechanism:
  33. Syntax
  34.  q‘[QUOTE_CHAR]Text[QUOTE_CHAR]’
  35.  Make sure that the QUOTE_CHAR doesnt exist IN the text.
  36. SELECT q‘{This is Orafaq’s ‘quoted’ text FIELD}‘ FROM DUAL;

You may also like