CODEWITHSUNDEEP
sqlsql-servert-sqlsql-update
CraigBob
CraigBob

Reputation: 157

How can I update a field based on a substring of another field?

I'm trying to run an update statement based on the contents of a For XML PATH field. The SQL is:

UPDATE a
SET a. ItemsList =
(select distinct Items + ',' from #tScanRemake b WHERE a.ScanOrderCaseid= b.ScanOrderCaseid FOR XML PATH( '') )
FROM #tScanRemake a

UPDATE a
SET a. IsBridge = 1  Select Count (*) from (Select * From #tScanRemake b WHERE CHARINDEX ('Bridge', ItemsList)> 0 )
FROM #tScanRemake a

The 1st portion works great and gives me a ,separated list in the ItemsList field. What I want to do is update a bit field based on a substring in the ItemsList field.

The issue is the substring can occur more than once in the list. I only care if it occurs at all. If the string is found then I want to set the IsBridge field to 1 otherwise set it to 0.

I can't figure out how to search within the list.

Upvotes: 0

Views: 433

Answers (1)

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

You can use CASE condition in your UPDATE statement

UPDATE a
SET a.IsBridge = CASE WHEN CHARINDEX ('Bridge', ItemsList)> 0 THEN 1 ELSE 0 END
FROM #tScanRemake a

Upvotes: 2

Related Questions