I am using SQL Server 2012. My input string is
'data1''data2''data3''data4''data5'
'data1','data2','data3','data4','data5'
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
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