How to Anticipate and Escape Single Quote ' in Oracle

How to anticipate and escape single quote ' in oracle

The best way is to use the quoting string literal technique. The syntax is q'[...]', where the "[" and "]" characters can be any of the following as long as they do not already appear in the string.

• !
• [ ]
• { }
• ( )
• < >

You don't have to worry about the single-quotation marks within the string.

Suppose i have a column value as aaa'gh

So you could simply write the SQL as,

SELECT q'[aaa'gh]' FROM DUAL;

It saves a lot of time for developers. Gone are those days when we(developers) used to verify the dynamic sql using dbms_output in development DB, just to make sure things are at place before moving into production.

Escape single quotes from comma separated string in Oracle

'6218, 5577' this is a string and not a list of values. So if you do select REPLACE('6218, 5577','''','''''') from dual; you are trying to replace single Quote in your string. since it does not exists in your string, nothing will be replaced.

the result of you select is still the same string and not a list as you expect.

you should split a comma delimited string in rows.

here is one way to do that

with tab as (
  SELECT trim(regexp_substr('6218, 5577', '[^,]+', 1, LEVEL)) str
   FROM dual
   CONNECT BY instr('6218, 5577', ',', 1, LEVEL - 1) > 0
) 

than you can use it on your select

with tab as (
  SELECT trim(regexp_substr('6218, 5577', '[^,]+', 1, LEVEL)) str
   FROM dual
   CONNECT BY instr('6218, 5577', ',', 1, LEVEL - 1) > 0
) 
select ...
  from ...
  WHERE dm."branch_id" IN (select str from tab ); 

How to handle a single quote in Oracle SQL with long text

Try with the Q' operator; for example:

create table t_clob ( a clob)

insert into t_clob values (q'[START aa'a''aa aa 'a'' aa'a'  a'a' a END]')

How to select single quote mark exist records using oracle regex

You can escape a quote in Oracle by doubling it.

So, using the regular LIKE operator:

SELECT * 
FROM student 
WHERE address LIKE '%''%'

With REGEXP_LIKE you'd have to perform a similar escaping:

SELECT * 
FROM student 
WHERE  regexp_like (address, '''+')

How to use single quote twice in constructing select statement

As others have mentioned, there is no need of dynamic SQL here.

However, if you just want to know how to use single-quotes for learning purpose, here is an example,

SQL> SET serveroutput ON
SQL> DECLARE
  2    val_name VARCHAR2(10);
  3    ta_tab   VARCHAR2(10);
  4    v_str    VARCHAR2(100);
  5    A        VARCHAR2(10);
  6    b        VARCHAR2(10);
  7    c        VARCHAR2(10);
  8    d        NUMBER;
  9  BEGIN
 10    val_name := 'LALIT';
 11    ta_tab   := 'TEST';
 12    v_str    :='SELECT '''||val_name||''', ename,'''||ta_tab||''', sal from emp where empno = 7788';
 13    EXECUTE IMMEDIATE v_str INTO A,b,c,d;
 14    dbms_output.put_line(a||'-'||b||'-'||c||'-'||d);
 15  END;
 16  /
LALIT-SCOTT-TEST-3000

PL/SQL procedure successfully completed.

There are other things to keep in mind, like using bind variables etc. however, it is out of scope of this topic.

Use of single quote and double 'single quote' in PL/SQL block

From the Oracle documentation on text literals:

In the top branch of the syntax:

• c is any member of the user's character set. A single quotation mark (') within the literal must be preceded by an escape character.
  To represent one single quotation mark within a literal, enter two
  single quotation marks.

In your original version, the single quote immediately before the 12.08.2013 is treated as the end of that string - how would it know to treat it any other way? The 12.08 then becomes a number, but it's not in a useful place, so the parser doesn't know what to do with it, so you get an error.

In the second version you've escaped the quotes that are part of the actual text value, so Oracle knows they are part of the text, not marking the end of it. When it reaches the lone single quote before the semi-colon it sees that as the end of the string, which is what you want.

As @Parado says, try displaying the escape-quoted version and you'll see that it appears in a form that you could run directly, with the escaped single quotes appearing as strings in their own right as part of your create statement.

You do need to escape all the single quotes, but you might find the alternative quoting syntax easier, as described in the second branch in the documentation. In your case that would be:

STR := q'[ CREATE TABLE TNAME AS
SELECT ... FROM INPUT_TABLE IP
WHERE ((IP.DATE_FIELD = TO_DATE('12.08.2013', 'DD.MM.sYYYY'))) ]';

This makes the quoted text literal easier to read and you don't have to worry about catching and escaping all the single quotes within it. You just need to make sure you pick a quote delimiter that doesn't appear in the text. Displaying that will look exactly the same as your escape-quoted version.

How do I deal with quotes ' in SQL

The escape character is ', so you would need to replace the quote with two quotes.

For example,

SELECT * FROM PEOPLE WHERE SURNAME='O'Keefe'

becomes

SELECT * FROM PEOPLE WHERE SURNAME='O''Keefe'

That said, it's probably incorrect to do this yourself. Your language may have a function to escape strings for use in SQL, but an even better option is to use parameters. Usually this works as follows.

Your SQL command would be :

SELECT * FROM PEOPLE WHERE SURNAME=?

Then, when you execute it, you pass in "O'Keefe" as a parameter.

Because the SQL is parsed before the parameter value is set, there's no way for the parameter value to alter the structure of the SQL (and it's even a little faster if you want to run the same statement several times with different parameters).

I should also point out that, while your example just causes an error, you open youself up to a lot of other problems by not escaping strings appropriately. See http://en.wikipedia.org/wiki/SQL_injection for a good starting point or the following classic xkcd comic.

---

Related Topics