We help IT Professionals succeed at work.

single quote problem

ewang1205
ewang1205 asked
on
The following code doesn't work.  I have to use a defined varible my_long_string and the value could be anything like TE'ST, TES'T ....   Is there a simple way to make this work in the select statement and find my_desc ='TE'ST';?  Thanks.

define my_long_string='TE'ST';
select * from emp  where  emp_desc = '&my_long_string';
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
what about this:

define my_long_string='TE\'ST';
define my_long_string='TE''ST';

Author

Commented:
Doesn't work.  Have you tried?  

Author

Commented:
I also tried the following and not working.
define my_long_string='TE'''ST';
define my_long_string='TE''''ST';
Replace any single quote contained in the variable with a double quote before using in the query statement. This is how it is done in SQL, not sure about Oracle.

The attached example is c#
 

myvariable.Replace("'","''");

Open in new window

Top Expert 2005
Commented:
SQL> define test2='TE'''||''||'''ST'
Production Manager / Application Support Manager
CERTIFIED EXPERT
Commented:
Please see the below. It is working fine for me. Can you try the below :

SQL> define my_long_string=TE''ST                      
-- two single quotes and not a double quote.
SQL> select * from emp  where  emp_desc = '&my_long_string';

======================

For your understanding, it is working as shown below for me. I have used some temp table to query as you have shown.

SQL> define my_long_string=TE''ST                      
-- two single quotes and not a double quote.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TESTCOL                                            VARCHAR2(20)

SQL> insert into test values('TE''ST');

1 row created.

SQL> select * from test;

TESTCOL
--------------------
TE'ST

SQL> select * from test where testcol = '&my_long_string';
old   1: select * from test where testcol = '&my_long_string'
new   1: select * from test where testcol = 'TE''ST'

TESTCOL
--------------------
TE'ST

SQL>
Commented:
declare @my_long_string varchar(25)
set @my_long_string = 'Tes''T'
define my_long_string='TE' || CHR(39) || 'ST';
select * from emp  where  emp_desc = '&my_long_string';

Explore More ContentExplore courses, solutions, and other research materials related to this topic.