hi hope this helps
solved = yes
why am i doing this ?
= tally table approach
= tally table eliminates loops and recursion. scales well for longer strings or larger datasets
= performance optimized
create sample script
drop table if exists SampleData;
CREATE TABLE SampleData (
Id INT IDENTITY(1,1) PRIMARY KEY,
Codes VARCHAR(200) null
);
INSERT INTO SampleData (Codes)
VALUES ('1E1,ASSY,BLU,2R2,5D2'),
('3C3,XYZ,4N4'),
('NONE'),
('7G7,HELLO'),
(NULL);
Data
t-sql
;WITH Tally AS (
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM sys.objects
)
SELECT t.ID, x.token
FROM SampleData t
CROSS APPLY (
SELECT LTRIM(RTRIM(SUBSTRING(t.Codes, n.N, ca.Pos - n.N))) AS token
FROM Tally n
CROSS APPLY (SELECT CHARINDEX(',', t.Codes + ',', n.N) AS Pos) ca
WHERE SUBSTRING(t.Codes, n.N, 1) <> ','
AND (n.N = 1 OR SUBSTRING(t.Codes, n.N-1, 1) = ',')
AND SUBSTRING(t.Codes, n.N, ca.Pos - n.N) LIKE '%[0-9]%'
) x;
Result
