Split String Update Query

1

Hi All,

I try to split a string into a table, The table is already present so it should use an Update statement.

Delimiter is the '-' char.

Source table,

ID            Reference                            ReferenceUnsplitted
1                    Ref1                                         1-2-3   
1                    Ref2                                         1-2-3
1                    Ref3                                         1-2-3
2                    RefA                                         ABSC - SASD    
2                    RefB                                         ABSC - SASD

Result should be

ID            Reference                            ReferenceUnsplitted
1                    Ref1-1                                        1-2-3   
1                    Ref2-2                                        1-2-3
1                    Ref3 -3                                        1-2-3
2                    RefA -ABSC                                ABSC - SASD    
2                    RefB-SASD                                 ABSC - SASD

As far a i got so far,

UPDATE TableS
 SET Reference= CONCAT(Reference, '-',  
		(SELECT * FROM string_split(ReferenceUnsplitted, '-')))

But the query results in multiple columns so this would not work.

Can anyone point me in the right direction? Thanks in advance!

2

hi hope this helps

create data script

drop table Temp

create table Temp (ID int, Reference varchar(10))
insert into Temp select 1 ,'Ref1'
insert into Temp select 1 ,'Ref2'
insert into Temp select 1 ,'Ref3'
insert into Temp select 2 ,'RefA'
insert into Temp select 2 ,'RefB'

select * from Temp

select 
    b.id
  , b.Reference
  , a.ReferenceUnsplitted 
from 
   ( select ID, STRING_AGG(replace(reference,'Ref',''), '-')  as ReferenceUnsplitted from temp group by ID ) a 
     join 
   Temp b on a.id = b.id

image

3

Thank you for your reply,

I meant it the other way arround. Perhaps i have explained it incorrect

So if in colum reference was only the text ref it should be added with an 1, 2, and 3.

Data will look like below then:

ID               Reference            ExtraReference
1                     Ref                     1;2;3
1                     Ref                     1;2;3
1                     Ref                     1;2;3
2                     Ref                     A ;B
2                     Ref                     A;B

Result will look like below then:

Data will look like below then:

ID               Reference            ExtraReference
1                     Ref 1                    1
1                     Ref 2                    2
1                     Ref 3                    3
2                     Ref A                    A  
2                     Ref B                    B
4 
SELECT DISTINCT
	T.ID
	,T.Reference + X.[Value] AS Reference
	,X.[Value] AS ExtraReference
FROM YourTable T
	CROSS APPLY STRING_SPLIT(ExtraReference, ';') X;
5

If your really want to do an update the big problem is your table does not have a primary key.
(This is not allowed in relational theory.)
To get around this you are going to have to add a column to make a primary key and use a string split function that returns an index. As the enable_ordinal option for STRING_SPLIT is only available in Azure and SQL2022 I would use:

WITH TableWithPK
AS
(
	-- ID, MakePK will be the primary key for the update
	SELECT ID, Reference, ExtraReference
		,ROW_NUMBER() OVER (PARTITION BY ID, Reference ORDER BY ID) AS MakePK
	FROM YourTable
)
,ExtraSplits
AS
(
	SELECT DISTINCT T.ID, T.Reference, X.ItemNumber, X.Item
	FROM YourTable T
		CROSS APPLY dbo.DelimitedSplit8K(ExtraReference, ';') X
)
UPDATE P
SET Reference = P.Reference + S.Item
	,ExtraReference = S.Item
FROM TableWithPK P 
	JOIN ExtraSplits S
		ON P.ID = S.ID
			AND P.MakePK = S.ItemNumber
-- make idempotent
WHERE RIGHT(P.Reference, LEN(P.extraReference)) <> P.extraReference;

select * from YourTable;
1 Like