amol amol - 6 months ago 26
SQL Question

SQL Server : REPLACE function replace ' ' with ',' in string

I am using SQL Server 2012. My input string is

'data1''data2''data3''data4''data5'


I want output to be:

'data1','data2','data3','data4','data5'


I tried the following

DECLARE
@P_NAME VARCHAR(20),
@V_NAME_1 VARCHAR(20);

SET @P_NAME = 'E056''E056''E056''E056''E056'

SET @V_NAME_1 = REPLACE(@P_NAME,'''',''',''')

SELECT @V_NAME_1


but it does not return the expected output.

Please let me know what I am missing.

Thanks,
Amol

Answer

You need to increase the length of your VARCHAR, I've set it to 50 below. Little tweak to your SET statement too. I've changed the values for testing;

DECLARE 
    @P_NAME VARCHAR(50), 
    @V_NAME_1 VARCHAR(50);

SET @P_NAME='E056''E057''E058''E059''E060'

SET @V_NAME_1=''''+REPLACE(@P_NAME,'''',''',''')+''''

SELECT @V_NAME_1