Remove specific word in comma seperate string in MS SQL

Question

Here is my column looks like, named Sets.

My Column:

{C=Pass, D=Fail, E=Pass, F= Pass, G=Pass}
{C=Pass, D=Fail, E=NoApplication}

Expected

{C=Pass,  E=Pass, F= Pass, G=Pass}
{C=Pass,  E=NoApplication}

I wish to delete the "Fail" in my column. Please help.

what are you doing to get this ? Can you post your code ? – Muhammad Usman Oct 20 '16 at 07:16 — Muhammad Usman, Oct 20 '16 at 07:16

Jaydip Jadhav · Answer 1 · 2016-10-20T07:33:52.667

0

You may looking for Update Command

UPDATE TableName
SET Name = CASE WHEN CHARINDEX('=Fail,',Name ) > 1
                THEN STUFF(Name,CHARINDEX('=Fail,',Name)-1,LEN('=Fail,')+1,'')
                ELSE Name 
            END

edited Oct 20 '16 at 07:33

answered Oct 20 '16 at 07:19

Jaydip Jadhav

12,179
6
24
40

It works, but what if "E=Fail"? I had a lot of rows... with " x = Fail" – wow5 Oct 20 '16 at 07:24
Yes. x can be any string. – wow5 Oct 20 '16 at 07:28
It only can delete one time x=Fail, as my column do contains more than 1 x=Fail. – wow5 Oct 20 '16 at 07:49

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

You can try this

UPDATE Use my approach within an updateable CTE

Changes the value in your table. Just replace @tbl with your actual table's name and `YourColumn with the column's name. Carefull with real data!

DECLARE @tbl TABLE(YourColumn VARCHAR(100));
INSERT INTO @tbl VALUES
 ('{C=Pass, D=Fail, E=Pass, F= Pass, G=Pass}')
,('{C=Pass, D=Fail, E=NoApplication}');

WITH Casted AS
(
    SELECT CAST('<x>' + REPLACE(SUBSTRING(t.YourColumn,2,LEN(YourColumn)-2),', ','</x><x>') + '</x>' AS xml) AsXml
            ,t.YourColumn AS OldValue
    FROM @tbl AS t
)
,UpdateableCTE AS
(
SELECT '{'
     + STUFF((
        SELECT ', ' + a.value('.','nvarchar(100)')
        FROM Casted.AsXml.nodes('/x') AS A(a)
        WHERE RIGHT(a.value('.','nvarchar(100)'),4)<>'Fail'
        FOR XML PATH('')
       ),1,2,'')
     + '}' AS NewValue
     ,OldValue
FROM Casted
)
UPDATE UpdateableCTE SET OldValue=NewValue;

SELECT * FROM @tbl;

The string is splitted into its elements. Then it is re-concatenated without the ones with "Fail".

@wow5 I'm glad to read this! Pleaes allow me one tiny hint: Thx for the acceptance! If you don't mind it would be nice to - additionally - vote on answers. Since you've crossed the 15 rep points border yourself you are asked to vote. Votes are even more important than acceptance as they are counted for badges and privileges. — Shnugo, Oct 20 '16 at 09:01

Remove specific word in comma seperate string in MS SQL

2 Answers2

UPDATE Use my approach within an updateable CTE