0
2668

Written by

CM Wang
Follow
Question CM Wang · Jul 3, 2017

How to escape the single quote in the single quoted string

#Caché #SQL

I have a stored procedure like below:

Call USER.SP('select * from Sample.Person where SSN='aaaaa'','0','S')

How could I escape the single quotes for aaaaa? I try double single quotes but in vain.

Thanks you.

Discussion (11)0
Log in or sign up to continue

Comments

Mark Hanson · Jul 3, 2017

$replace is your friend here so you can just do:

set escaped=$replace(str,”’”,”’’”)

You will need to parse out the single quote at the start of the statement so you do not double this quote too. 

0
Vitaliy Serdtsev · Jul 3, 2017

But so?

Call USER.SP('select * from Sample.Person where SSN=''aaaaa''','0','S')
0
CM Wang  Jul 3, 2017 to Vitaliy Serdtsev

The double single quote does not work.

0
Dmitry Maslennikov  Jul 5, 2017 to CM Wang

It's a bit difficult to answer correctly because I still not sure what are you doing, I would try ''''aaa''aaa'''' or ''''aaa''''''aaa'''' or  even ''''aaa''''''''aaa''''

0
Andrei Luiz Nenevê · Jul 3, 2017

Hi,

You must use 3 single quotes:

Call USER.SP('select * from Sample.Person where SSN='''aaaaa''' ','0','S')
0
CM Wang  Jul 3, 2017 to Andrei Luiz Nenevê

I try triple single quote in $system.SQL.Shell(), but it still does not work.

ERROR #5540: SQLCODE: -1 Message:  ) expected, ? found^ Call USER. SP ( ? ? [Generate+1247^%SYS.DynamicStatement:SAMPLES]

0
Dmitry Maslennikov · Jul 3, 2017

So strange query. But anyway if you want to get double single quotes, should double each one. So, it should be ''''

Something like this.

Call USER.SP('select * from Sample.Person where SSN=''''aaaaa'''' ','0','S')
0
CJ H  Jul 3, 2017 to Dmitry Maslennikov

Thanks, it works now.

0
Dmitry Maslennikov  Jul 4, 2017 to CJ H

If so, do not forget to mark the answer as accepted.

0
CM Wang  Jul 5, 2017 to Dmitry Maslennikov

BTW, what if i would like to compare SSN with aaa'aa (ie: single  quote is also the content  I would like to compare.) ?

Thanks.

0