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

Question

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

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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

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

Sign up