cyrus2500 cyrus2500 - 1 month ago 10
SQL Question

How to do add separator , between special content in sql this example?

How to do add separator (,) between one cell in sql this example?

declare @htmlXML xml = N'<div class="screen_specs_container " >

<div class="left_specs_container">Compatibility:</div>
<div class="right_specs_container " ><h4 >Dell STUDIO 17</h4></div><div class="left_specs_container" data-filterLabel="Replacement Part Type:">Replacement Part Type</div><div class="right_specs_container spec_highlight"> LCD Screen Only<a class="thickbox" title="" href="/laptopscreen_viewer.php?edit_language=English&amp;view_article=1468010881&amp;height=500&amp;width=800"><img border="0" valign="middle" alt="?" src="/images/question2.gif"/></a></div><div class="left_specs_container"></div><div class="right_specs_container" style=" max-width: 250px;"><font color="red">ONLY FOR <a href="https://youtu.be/4F0xPuz4Jnw>" >LED VERSION</a> WILL NOT WORK FOR CCFL!</font></div><div class="left_specs_container" data-filterLabel="Size:">Size:</div><div class="right_specs_container" title="size">17-inch WideScreen (14.4inch x9inch )<a class="lightbox" title="" href="//www.youtube.com/embed/_zalTiJ1vFY?autoplay=1"><img border="0" valign="middle" alt="?" src="/images/question2.gif" /></a></div><div class="left_specs_container" data-filterLabel="Resolution:">Resolution:</div><div class="right_specs_container" title="resolution">WXGA+ (1440x900)<a class="lightbox" title="" href="//www.youtube.com/embed/WWPSXibVLkg?autoplay=1"><img border="0" valign="middle" alt="?" src="/images/question2.gif" /></a></div><div class="left_specs_container" data-filterLabel="Surface Type:">Surface Type:</div><div class="right_specs_container" title="type"><font color="red">Glossy<a class="lightbox" title="" href="//www.youtube.com/embed/gt_CSbMF-5o?autoplay=1"><img border="0" valign="middle" alt="?" src="/images/question2.gif" /></a></font></div><div class="left_specs_container" style="display:none" data-filterLabel="Touchscreen Panel:">Touchscreen Panel:</div><div class="right_specs_container" style="display:none">Not Included</div><div class="left_specs_container" data-filterLabel="Backlight type:">Backlight type:</div><div class="right_specs_container" title="backlight-type">LED<a class="lightbox" title="" href="//www.youtube.com/embed/4F0xPuz4Jnw?autoplay=1"><img border="0" valign="middle" alt="?" src="/images/question2.gif" /></a></div><div class="left_specs_container">Video signal connector:</div><div class="right_specs_container">LED 50 pin screen [<a target="blank" class="thickbox laptops" title="Video Connector" href="/img/connectors/1_big/LED50pins.jpg">image</a>]</div><div class="left_specs_container">Condition:</div><div class="right_specs_container">New Grade A+<a class="thickbox" title="" href="/laptopscreen_viewer.php?edit_language=English&amp;view_article=1243967715&amp;height=500&amp;width=800"><img border="0" valign="middle" alt="?" src="/images/question2.gif"/></a></div><div class="left_specs_container">Warranty:</div><div class="right_specs_container">3 Years</div><div class="left_specs_container" style="color:f00">Note:</div><div class="right_specs_container">Make sure new screen has same size, resolution, backlight as your old one!</div></div>'
;


SELECT
t.v.value('.','nvarchar(max)') as [Description]
FROM @htmlXML.nodes('/div[1]') as t(v)


now output:

Compatibility:Dell STUDIO 17Replacement Part Type LCD Screen OnlyONLY FOR LED VERSION WILL NOT WORK FOR CCFL!Size:17-inch WideScreen (14.4inch x9inch )Resolution:WXGA+ (1440x900)Surface Type:GlossyTouchscreen Panel:Not IncludedBacklight type:LEDVideo signal connector:LED 50 pin screen [image]Condition:New Grade A+Warranty:3 YearsNote:Make sure new screen has same size, resolution, backlight as your old one!


The desired output:(add , and : and delete special content)

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


thanks for tips my friends....................................................................................................................................................................

Answer

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,
    replace(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, '');

select @Description as [Description];

declare @ReplacementPartType nvarchar(max);
set @ReplacementPartType = @Xml.query('/xml/x[@type="Replacement Part Type"]').value('.','nvarchar(max)');
select @ReplacementPartType as [ReplacementPartType];