Oracle pl-sql escape character for a

Question

When I am trying to execute INSERT statement in oracle  I got SQL Error  ORA-00917  missing comma error because there is a value as Alex s Tea Factory in my INSERT statement    How could I escape

User · Answer

you can use ESCAPE like given example below

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.

SELECT name FROM emp WHERE id LIKE '%/_%' ESCAPE '/';

The same works inside PL/SQL:

 if( id like '%/_%' ESCAPE '/' )

This applies only to like patterns, for example in an insert there is no need to escape _ or %, they are used as plain characters anyhow. In arbitrary strings only ' needs to be escaped by ''.

User · Answer

Instead of worrying about every single apostrophe in your statement  You can easily use the q  Notation   Example  SELECT q  Alex s Tea Factory   FROM DUAL    Key Components in this notation are   q  which denotes the starting of the notation   an optional symbol denoting the starting of the statement to be  fully escaped  Alex s Tea Factory  Which is the statement itself     A closing parenthesis with a apostrophe denoting the end of the notation    And such that  you can stuff how many apostrophes in the notation without worrying about each single one of them  they re all going to be handled safely   IMPORTANT NOTE  Since you used   you must close it with     and remember it s optional to use any other symbol  for instance  the following code will run exactly as the previous one  SELECT q  Alex s Tea Factory   FROM DUAL

User · Answer

In SQL  you escape a quote by another quote   SELECT  Alex  s Tea Factory  FROM DUAL

User · Answer

Here is a way to easily escape  amp  char in oracle DB set escape       and within query write like  ERRORS  amp     PERFORMANCE

User · Answer

To escape it  double the quotes   INSERT INTO TABLE A VALUES    Alex  s Tea Factory

User · Answer

Your question implies that you re building the INSERT statement up by concatenating strings together   I suggest that this is a poor choice as it leaves you open to SQL injection attacks if the strings are derived from user input   A better choice is to use parameter markers and to bind the values to the markers   If you search for Oracle parameter markers you ll probably find some information for your specific implementation technology  e g  C  and ADO  Java and JDBC  Ruby and RubyDBI  etc    Share and enjoy

User · Answer

SELECT q  Alex s Tea Factory   FROM DUAL

[oracle] Oracle pl-sql escape character (for a " ' ")

Examples related to oracle

Examples related to escaping

Examples related to oracle11g