CODEWITHSUNDEEP
sqlsql-server
scapegoat17
scapegoat17

Reputation: 5831

Write a script to update a nvarchar field to have delimiters

I seem to be having an issue with wrapping my head around how to go about writing an update script and was wondering if someone would be able to help me.

I have a field that has inconsistent data in it that looks something like this:

MyField
|123|456|789|
|789|784|154|
879|487|784
789|754|877

I need to write a script add the missing | in the beginning and the end of each field. I also need to make sure that it doesn't add the | if it is already there.

Would anyone be able to provide me with some assistance?

Upvotes: 0

Views: 35

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

I would much rather see you normalize this instead of causing yourself more pain dealing with a denormalized delimited list but the update is pretty trivial.

Update SomeTable
set MyField = case when LEFT(MyField, 1) = '|' then '' else '|' end + MyField + case when Right(MyField, 1) = '|' then '' else '|' end

Upvotes: 2

Related Questions