[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2254
  • Last Modified:

single quote problem

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';
0
ewang1205
Asked:
ewang1205
6 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:

define my_long_string='TE\'ST';
define my_long_string='TE''ST';
0
 
ewang1205Author Commented:
Doesn't work.  Have you tried?  
0
 
ewang1205Author Commented:
I also tried the following and not working.
define my_long_string='TE'''ST';
define my_long_string='TE''''ST';
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
rhythmluvrCommented:
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

0
 
jwittenmCommented:
SQL> define test2='TE'''||''||'''ST'
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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>
0
 
ursangelCommented:
declare @my_long_string varchar(25)
set @my_long_string = 'Tes''T'
0
 
Jinesh KamdarCommented:
define my_long_string='TE' || CHR(39) || 'ST';
select * from emp  where  emp_desc = '&my_long_string';
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now