Add Separator Between Special Sql Content

How to do add separator (,) between one cell in sql this example? declare @htmlXML xml = N'

Co

Solution 1:

Just using STUFF & FOR XML PATH to generate the comma separated string:

declare @Description nvarchar(max);

WITH Divs AS 
(
    SELECT 
    row_number() over (order by (select 0)) as rn,
    replace(replace(replace(x.div.value('.','nvarchar(max)'),CHAR(13),''),CHAR(10),''),'  ','') as val
    from @htmlXML.nodes('/div/div') x(div)
)
select @Description = STUFF ((
    select
    rtrim(concat(' , ',divs1.val,' ',divs2.val))
    from Divs divs1
    left join Divs divs2
      on (divs1.rn+1 = divs2.rn)
    where divs1.rn%2>0
    FOR XML PATH('')
), 1, 3, '');

select @Description as [Description];

If you want to get individual elements from it also, then first transforming to an easier to read XML might be worth it.

For example:

declare @Xml xml;

WITH Divs AS 
(
    SELECT 
    row_number() over (order by (select 0)) as rn,
    ltrim(rtrim(replace(replace(x.div.value('.','nvarchar(max)'),CHAR(13),''),CHAR(10),''))) as val
    from @htmlXML.nodes('/div/div') x(div)
)
select @Xml = ( 
    select
    divs1.val as "@type", rtrim(divs2.val) as "data()"
    from Divs divs1
    left join Divs divs2
      on (divs1.rn+1 = divs2.rn)
    where divs1.rn%2>0
    FOR XML PATH('x'), root ('xml')
);

select @Xml as x;

declare @Description nvarchar(max);
/*
select @Description = STUFF ((
    select concat(' ,',x.p.value('@type','nvarchar(max)'),' ',x.p.value('.','nvarchar(max)'))
    from @Xml.nodes('/xml/x') x(p)
    FOR XML PATH('')
), 1, 2, '');
*/

set @Description = concat(
 'Compatibility: ',@Xml.query('/xml/x[@type="Compatibility:"]').value('.','nvarchar(max)')
,', Replacement Part Type: ',@Xml.query('/xml/x[@type="Replacement Part Type"]').value('.','nvarchar(max)')
,', Size: ',@Xml.query('/xml/x[@type="Size:"]').value('.','nvarchar(max)')
,', Resolution: ',@Xml.query('/xml/x[@type="Resolution:"]').value('.','nvarchar(max)')
,', Surface Type: ',@Xml.query('/xml/x[@type="Surface Type:"]').value('.','nvarchar(max)')
,', Touchscreen Panel: ',@Xml.query('/xml/x[@type="Touchscreen Panel:"]').value('.','nvarchar(max)')
,', Backlight type: ',@Xml.query('/xml/x[@type="Backlight type:"]').value('.','nvarchar(max)')
,', Video signal connector: ',replace(@Xml.query('/xml/x[@type="Video signal connector:"]').value('.','nvarchar(max)'),'[image]','')
);

select @Description as [Description];    

Returns :

Compatibility: Dell STUDIO 17, Replacement Part Type: LCD Screen Only, Size: 17-inch WideScreen (14.4inch x9inch ), Resolution: WXGA+ (1440x900), Surface Type: Glossy, Touchscreen Panel: Not Included, Backlight type: LED, Video signal connector: LED 50 pin screen

Share