Adding single quotes in a string

Question

Post reply

Adding single quotes in a string

SQL006

SSCertifiable

Points: 5071
More actions
April 21, 2016 at 6:24 am

#313434

Hi
DECLARE @Codes nvarchar(500) = 'AZJ,CLC,AZF,DDD'
-- need result like this
'AZJ','CLC','AZF','DDD'
without substring function is it possible to get the result.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You just need to use REPLACE and escape the single quotes.

DECLARE @Codes nvarchar(500) = 'AZJ,CLC,AZF,DDD'

SELECT REPLACE(@Codes, ',', ''',''')

Are you doing this for dynamic sql coding? Are you aware of sql injection and all the perils it involves?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Phil Parkin SSC Guru Points: 246988 More actions · Answer 2

Luis Cazares (4/21/2016)
You just need to use REPLACE and escape the single quotes.
DECLARE @Codes nvarchar(500) = 'AZJ,CLC,AZF,DDD'
SELECT REPLACE(@Codes, ',', ''',''')
Are you doing this for dynamic sql coding? Are you aware of sql injection and all the perils it involves?

You missed the first and last.

DECLARE @Codes NVARCHAR(500) = 'AZJ,CLC,AZF,DDD';

SELECT CONCAT('''', REPLACE(@Codes, ',', ''','''), '''');

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

NJ Smith SSC Enthusiast Points: 180 More actions · Answer 3

if you are working with SQL Server 2016 then there is a new function called STRING_SPLIT ( string , separator )

Phil Parkin SSC Guru Points: 246988 More actions · Answer 4

NJ Smith (4/21/2016)
if you are working with SQL Server 2016 then there is a new function called STRING_SPLIT ( string , separator )

Indeed there is. But I see no string splitting requirement here.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Phil Parkin (4/21/2016)
Luis Cazares (4/21/2016)
You just need to use REPLACE and escape the single quotes.
DECLARE @Codes nvarchar(500) = 'AZJ,CLC,AZF,DDD'
SELECT REPLACE(@Codes, ',', ''',''')
Are you doing this for dynamic sql coding? Are you aware of sql injection and all the perils it involves?
You missed the first and last.
DECLARE @Codes NVARCHAR(500) = 'AZJ,CLC,AZF,DDD';
SELECT CONCAT('''', REPLACE(@Codes, ',', ''','''), '''');

Need coffee now.

Must not post before morning coffee.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQL006 SSCertifiable Points: 5071 More actions · Answer 6

Thank you guys

No i am not using dynamic SQL, just passing it to storeprocedure as a parameter which is used in 'IN' operator.

one doubt does REPLACE has any performance impact.

Thanks

Phil Parkin SSC Guru Points: 246988 More actions · Answer 7

SQL006 (4/21/2016)
Thank you guys
No i am not using dynamic SQL, just passing it to storeprocedure as a parameter which is used in 'IN' operator.
one doubt does REPLACE has any performance impact.
Thanks

Every single operation has 'a performance impact'. But IMO, REPLACE is considered fast in the SQL Server world.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

Luis Cazares (4/21/2016)
Phil Parkin (4/21/2016)
Luis Cazares (4/21/2016)
You just need to use REPLACE and escape the single quotes.
DECLARE @Codes nvarchar(500) = 'AZJ,CLC,AZF,DDD'
SELECT REPLACE(@Codes, ',', ''',''')
Are you doing this for dynamic sql coding? Are you aware of sql injection and all the perils it involves?
You missed the first and last.
DECLARE @Codes NVARCHAR(500) = 'AZJ,CLC,AZF,DDD';
SELECT CONCAT('''', REPLACE(@Codes, ',', ''','''), '''');
Need coffee now.
Must not post before morning coffee.

If it's being used with IN, then that's probably dynamic sql inside the stored procedure.

REPLACE, as any function needs resources to work, but the impact is minimal unless used on columns in WHERE or JOIN clauses which renders the queries non-SARGable.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 9

SQL006 (4/21/2016)
Thank you guys
No i am not using dynamic SQL, just passing it to storeprocedure as a parameter which is used in 'IN' operator.
one doubt does REPLACE has any performance impact.
Thanks

Based on that you're trying to do with the parameter, You either need to use dynamic sql or split the string into a table...

The following will NOT work...

DECLARE @Codes NVARCHAR(500) = N'AZJ,CLC,AZF,DDD';

DECLARE @CodesReformatted NVARCHAR(500) = CONCAT('''', REPLACE(@Codes, ',', ''','''), '''');

SELECT

*

FROM

dbo.SomeTable st

WHERE

st.Code IN (@CodesReformatted);