SQL Server 2012 Update Specific Column in CSV string

Question

I am looking for some help coding something. We are trying to update a specific "column" of the CSV string that I have.

Here's a screenshot to illustrate my point:

The column "DataRow" has a CSV string that I need to modify. It's easy to locate the first column using CHARINDEX, but I can't seem code to go beyond that. I am trying to update the 3rd value in the string, in case of the first record, would be 'TM - Desert Wind Villas..'.

Does anyone have any suggestions?

Thanks much in advance!

Answer 1

Before I give you the code, I want to say that change this design if you can!

A database is meant to have tables and columns. Using a delimited string violates normal form.

http://en.wikipedia.org/wiki/Database_normalization

Thus, I makes it a-lot harder to manipulating data (D.M.L. ~ UPD, DEL, INS).

With that CAUTION out of the way, I solved your problem using the following algorithm.

1 - Create and load sample table with data.
2 - Choose a string split function, see article from Aaron Bertrand on choices.
3 - Split the string and store Items by Key, Column number order in a staging table 1
4 - Update the staging table 1.  I did it for all column 2's.  
    However, there could be a where clause to do partial updates.  
    Also you could use update_flag to identify changed rows.
5 - Repackage the items as a string.  I saved this step in staging table 2.
    Can leverage update_flag to only packaged changed rows from staging table 1.
6 - Update the original table with new strings.

Here is the sample code for this exercise.

-- Just playing
use tempdb;

-- Drop the sample table 
if OBJECT_ID('my_data') > 0
drop table my_data
go 

-- Drop stage table 1
if OBJECT_ID('stage1') > 0
drop table stage1
go 

-- Drop stage table 2
if OBJECT_ID('stage2') > 0
drop table stage2
go 

-- Create sample table
create table my_data 
(data_id INT, data_delimited_txt VARCHAR(100));

-- Add data
insert into my_data
values (1, '1,ABC,HZY'),(2, '2,DEF,XYZ'),(3, '3,ABC,MNP');

-- Show the data
select * from my_data;
go

-- http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

-- String split function, save 2 stage1
select 
  d.data_id, 
  row_number() over (partition by d.data_id order by (select 0)) as row_id, 
  s.item 
into
  stage1
from 
  my_data d cross apply 
  msdb.dbo.SplitStrings_XML(d.data_delimited_txt, ',') as s
GO

-- Update second (col/val) in string
update stage1 
set item = 'NEW VALUE'
where row_id = 2
go

-- Get updated string in stage2
select 
  data_id, 
  stuff(
  (
    SELECT ', ' + ISNULL(inner1.item,'') 
    FROM #stage as inner1
    WHERE inner1.data_id = outer1.data_id
    FOR XML PATH('')
  ) , 1, 2, '') as list_txt
into stage2
from stage1 as outer1
group by data_id
go

-- Update original table
update my_data
set data_delimited_txt = list_txt
from my_data m join stage2 s on m.data_id = s.data_id
go

-- Show the data
select * from my_data;
go

I leave enhancements like an update flag (upd_flag) and any step elimination for you to do.

Also, the XML split function depends on valid XML data. A production release of this code should use another split function if that is a concern.

Good luck.

Answer 2

I guess you could use where clause something as following I am only SELECT(ing) data but obviously you can use Update statement to Update the returned data. ...

Test Data

DECLARE @T TABLE (ID INT, Data VARCHAR(100))
INSERT INTO @T 
VALUES (1, '1,ABC,HZY'),(2, '2,DEF,XYZ'),(3, '3,ABC,MNP')

SELECT * FROM @T

╔════╦════════════╗
║ ID ║    Data    ║
╠════╬════════════╣
║  1 ║ 1,ABC,HZY  ║
║  2 ║ 2,DEF,XYZ  ║
║  3 ║ 3,ABC,MNP  ║
╚════╩════════════╝

Query

SELECT *
FROM @T
WHERE (',' + Data + ',') LIKE '%MNP%'

╔════╦════════════╗
║ ID ║    Data    ║
╠════╬════════════╣
║  3 ║ 3,ABC,MNP  ║
╚════╩════════════╝