How to escape the single quote in the single quoted string

Question

Question

CM Wang · Jul 3, 2017

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

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 0

score 0 · Answer 1 · 2017-07-03T10:38:12-04:00

$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.

score 1 · Answer 2 · 2017-07-03T10:41:28-04:00

Vitaliy Serdtsev · Jul 3, 2017

But so?

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

1 0

score 0 · Answer 3 · 2017-07-03T11:38:53-04:00

CM Wang · Jul 3, 2017

The double single quote does not work.

0 0

score 0 · Answer 4 · 2017-07-03T16:08:17-04:00

I wonder if the problem is package name and it should be Sqluser instead of User.
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

score 0 · Answer 5 · 2017-07-05T11:17:15-04:00

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''''

score 0 · Answer 6 · 2017-07-03T12:06:48-04:00

Hi,

You must use 3 single quotes:

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

score 0 · Answer 7 · 2017-07-03T13:28:31-04:00

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]

score 0 · Answer 8 · 2017-07-03T16:28:13-04:00

CJ H · Jul 3, 2017

Thanks, it works now.

0 0

score 0 · Answer 9 · 2017-07-04T02:18:52-04:00

Dmitry Maslennikov · Jul 4, 2017

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

0 0

score 0 · Answer 10 · 2017-07-05T10:31:35-04:00

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.