Loop over a delimited list within one column

Question

A column in an app is being updated from storing a long string in a fixed-width column to a delimited column. Currently, there is one column that I write to a number of other columns based on the position by using substring().

Now that it is being updated to using a delimiter (~) to parse out each field within one column, I'm not sure how to extract the data and write them to individual fields in another table. Below is sample data and T-SQL:

Data

Radio $7.00~Shirt $79.99~Late Fee $9.95~Small Hat $7.00~Taxes $0.19

SQL:

INSERT INTO TempTable2 (Offer1, Offer2)
    SELECT 
        SUBSTRING(OfferString, 1, 81) AS Offer1, 
        SUBSTRING(OfferString, 82, 81) AS Offer2
    FROM 
        TempTable

In the fixed-width approach I could store each individual value to their own columns, per above, but not sure how to proceed with the new delimiter-based structure.

Can someone shed some light? Running on SQL Server 2008 R2.

Normalize your schema. See ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) (Spoiler: Yes, it is.). (And a fixed width list is equally as bad.) — sticky bit, Jul 29 '21 at 02:06
Agreed, and unfortunately I don't have control over how the data is stored. — saoco, Jul 29 '21 at 02:10
[Jeff Moden CSV String Splitter](https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%E2%80%9Ccsv-splitter%E2%80%9D-function). — HABO, Jul 29 '21 at 02:26

score 1 · Answer 1 · answered Jul 29 '21 at 02:05

1

More recent versions have a string_split() function. That allows this method:

INSERT INTO TempTable2 (Offer1, Offer2)
    SELECT s.Offer1, s.Offer2
    FROM TempTable t CROSS APPLY
         (SELECT MAX(CASE WHEN seqnum = 1 THEN value END) as offer1,
                 MAX(CASE WHEN seqnum = 2 THEN value END) as offer2
          FROM (SELECT s.value,
                       ROW_NUMBER() OVER (ORDER BY CHARINDEX('~' + s.value + '~', '~' + t.OfferString + '~') as seqnum
                FROM string_split(t.OfferString, '~') s
               ) s
         ) s;

Note that rumor has it that ORDER BY (SELECT NULL) will also preserve the original ordering. However, that functionality not documented.

answered Jul 29 '21 at 02:05

Gordon Linoff

1,242,037
58
646
786

Thanks but it's not compatible with SQL Server 2008 R2. – saoco Jul 29 '21 at 02:18
@saoco there are plenty of string split functions available if you have a nosy around. – Dale K Jul 29 '21 at 02:25
2

@saoco . . . And I should point out that SQL Server 2008 has not been supported since 2019. It is well past time to upgrade. – Gordon Linoff Jul 29 '21 at 12:14

score 1 · Accepted Answer · answered Jul 29 '21 at 02:30

1

Here is a 2008 version

Declare @YourTable table (ID int,OfferString varchar(500))
Insert Into @YourTable values
(1,'Radio $7.00~Shirt $79.99~Late Fee $9.95~Small Hat $7.00~Taxes $0.19')

Select A.ID
      ,Item  = ltrim(left(RetVal,charindex('$',RetVal+'$')-1))
      ,Value = convert(decimal(10,2),substring(RetVal,charindex('$',RetVal+'$')+1,25))
 From  @YourTable A
 Cross Apply (
                Select RetSeq = row_number() over (order by 1/0)
                      ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  ( values (cast('<x>' + replace((Select replace(OfferString,'~','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.'))) as A(x)
                Cross Apply x.nodes('x') AS B(i)
             ) B

Results

ID  Item        Value
1   Radio       7.00
1   Shirt       79.99
1   Late Fee    9.95
1   Small Hat   7.00
1   Taxes       0.19

answered Jul 29 '21 at 02:30

John Cappelletti

79,615
7
44
66

Despite what a whole lot of "holy grail" articles with improper testing conclude, this "XML Method" is a lot slower than a proper Tally-Based solution. – Jeff Moden Aug 20 '21 at 15:17
There are a lot of issues with what you say. Justifying slow code by saying it will only be used against small row sets leads to a "Death of a million small cuts" in databases. I'll also ask why anyone would use anything that they know is slower. @JohnCappelletti Last but not least, some poor slob is going to see the code and use it for something much larger. Too wit, I'm so tired of people that keep making lame excuses for not doing things right and thinking they can ignore performance because a single usage only looks at a small row set. Think big picture and how you're killing it., – Jeff Moden Aug 21 '21 at 17:03
Heh... ok. You go ahead and write slow but accurate code. I'll continue to write fast and accurate code. Think about it... if everything that takes only milliseconds in your server could suddenly perform in half that number of milliseconds, your server would be running twice as fast because almost everything that runs on a server is running in milliseconds. And, the XML splitter is actually 23 times slower than the Tally methods not to mention the XML method is easy to break. – Jeff Moden Aug 21 '21 at 18:31

Loop over a delimited list within one column

2 Answers2