top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How to escape special characters when building SQL queries?

+1 vote
108 views
How to escape special characters when building SQL queries?
posted Jun 15, 2015 by Suchithra

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

Escape quotes

Use two quotes for every one displayed. Examples:

SQL> SELECT 'Frank''s Oracle site' AS text FROM DUAL;

TEXT

Franks's Oracle site

SQL> SELECT 'A ''quoted'' word.' AS text FROM DUAL;

TEXT

A 'quoted' word.

SQL> SELECT 'A ''''double quoted'''' word.' AS text FROM DUAL;

TEXT

A ''double quoted'' word.

Escape wildcard characters

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:

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

SELECT name FROM emp WHERE id LIKE '%\%%' ESCAPE '\';

Escape ampersand (&) characters in SQL*Plus

When using SQL*Plus, the DEFINE setting can be changed to allow &'s (ampersands) to be used in text:

SET DEFINE ~ SELECT 'Lorel & Hardy' FROM dual;

Other methods:

Define an escape character:

SET ESCAPE '\' SELECT '\&abc' FROM dual;

Don't scan for substitution variables:

SET SCAN OFF SELECT '&ABC' x FROM dual;
answer Jun 16, 2015 by Manikandan J
Similar Questions
+1 vote

when ever i run the anonymous plsql block, sql*plus request me enter the parameter input value

I have a variable value like customer_name='xxxcust & lt pvt'

please help me

+3 votes

How to convert c # inline queries into stored procedure written in transact sql?

...