How to Handle a Single Quote in Oracle Sql

PL/SQL, how to escape single quote in a string?

You can use literal quoting:

stmt := q'[insert into MY_TBL (Col) values('ER0002')]';

Documentation for literals can be found here.

Alternatively, you can use two quotes to denote a single quote:

stmt := 'insert into MY_TBL (Col) values(''ER0002'')';

The literal quoting mechanism with the Q syntax is more flexible and readable, IMO.

How to escape single quotes in Oracle?

You need to escape the ' by doubling them :

select * from table where reason = '''missed transaction''';

How to handle a single quote in Oracle SQL

Use two single-quotes

SQL> SELECT 'D''COSTA' name FROM DUAL;

NAME
-------
D'COSTA

Alternatively, use the new (10g+) quoting method:

SQL> SELECT q'$D'COSTA$' NAME FROM DUAL;

NAME
-------
D'COSTA

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 handle a single quote in Oracle SQL?

I have a query constructed in Java to search and fetch records,when constructing with the values ' (Single Quote)(For Example:New's) then I'm not getting any results.

Then you're constructing it incorrectly. It sounds like you're using string concatenation. Never do that (see below). Instead, use PreparedStatement:

PreparedStatement ps = connection.prepareStatement(
    "SELECT FOO FROM BAR WHERE COLUMN LIKE ? ESCAPE \\"
);
ps.setString("this 'has' single quotes");
ResultSet rs = ps.executeQuery();

Your JDBC connector will ensure that the string is sent through correctly.

---

Re your comment saying you're using JPA: I don't know JPA, but this page suggests it would look something like:

TypedQuery<Thingy> query = em.createQuery(
    "SELECT FOO FROM BAR WHERE COLUMN LIKE :search ESCAPE \\",
    Thingy.class
);
return query.setParameter("search", "this 'has' quotes").getSingleResult();

---

Re "never do that":

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.

PL/SQL, how to write single quote in a string/function?

You need to escape ' and also remove q before second part of string. Square bracket in sql command will also make a problem if you're going to execute the command in Oracle

sql_3 := sql_3 || ' upper(name_p) like Upper(''%input_name%'')';

That's a bit weird but:

declare
  varr varchar2(100);
begin
  varr := 'aaa';
  varr := varr || q'[ upper(name_p) like Upper('%input_name%')]';
  dbms_output.put_line(varr);
end;
/

works on my machine.

Escaping single quote in PLSQL

I do this sort stuff a fair bit (usually generating insert/update statements).

You just need to use the replace function to turn all the ' into ''. i.e. Change it to:

str_comment:='COMMENT ON COLUMN '||rec.table_name||'.'||rec.column_name
            ||' IS '''||REPLACE( rec.description,'''','''''')||'''; ' ;

---

Related Topics