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!
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.
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 ║
╚════╩════════════╝
