amol amol - 1 year ago 62
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 Source

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