SQL Server 2012 finding text before and after delimiter and sometimes without delimiter

Question

I have been having fun with an issue where I need to break apart a string in SQL Server 2012 and test for values it may or may not contain. The values, when present, will be separated by up to two different ; symbols.

• When there is nothing, it will be blank.
• When there is a single value, it will show up without the delimiter.
• When there are two or more, up to 3, they will be separated by the delimiter.

As I said, if there is nothing in the record, it will be blank. Below are some example of how the data may come across:

' ',
'1',
'24',
'15;1;24',
'10;1;22',
'5;1;7',
'12;1',
'10;12',
'1;5',
'1;1;1',
'15;20;22'

I have searched the forums and found many clues, but I have not been able to come up with a total solution given all potential data values. Essentially, I would like to break it into 3 separate values.

1. text before the first delimiter or in the absence of the delimiter, just the text.

2. Text after the first delimiter and before the second in situation where there are two delimiters.

   The following has worked consistently:

   substring(SUBSTRING(Food_Desc, charindex(';', Food_Desc) + 1, 4), 0,
   charindex(';', SUBSTRING(Food_Desc, charindex(';', Food_Desc) + 1, 4))) as [Middle]
   

3. Text after the second delimiter in the even there are two delimiters and there is a third value

The main challenge is the fact that the delimiter, when present, moves depending on the value in the table. values 1-9 make it show up as the second character in the string, values 10-24 make it show up as the 3rd, etc.

Any help would be greatly appreciated.

Answer 1

This is simple if you have a well written t-sql splitter function. For this solution I'm using Jeff Moden's delimitedsplit8k.

sample data and solution

DECLARE @table table (someid int identity, sometext varchar(100));
INSERT @table VALUES (' '),('1'),('24'),('15;1;24'),('10;1;22'),
('5;1;7'),('12;1'),('10;12'),('1;5'),('1;1;1'),('15;20;22');

SELECT
  someid, 
  sometext,
  ItemNumber,
  Item
FROM @table
CROSS APPLY dbo.DelimitedSplit8K_LEAD(sometext, ';');

results

someid      sometext          ItemNumber  Item
----------- ----------------- ----------- --------
1                             1            
2           1                 1           1
3           24                1           24
4           15;1;24           1           15
4           15;1;24           2           1
4           15;1;24           3           24
5           10;1;22           1           10
5           10;1;22           2           1
5           10;1;22           3           22
6           5;1;7             1           5
6           5;1;7             2           1
6           5;1;7             3           7
7           12;1              1           12
7           12;1              2           1
8           10;12             1           10
8           10;12             2           12
9           1;5               1           1
9           1;5               2           5
10          1;1;1             1           1
10          1;1;1             2           1
10          1;1;1             3           1
11          15;20;22          1           15
11          15;20;22          2           20
11          15;20;22          3           22

Answer 2

Below is a modified version of a similar question How do I split a string so I can access item x?. Changing the text value for @sample to each of your possibilities listed seemed to work for me.

DECLARE @sample VARCHAR(200) = '15;20;22';
DECLARE @individual VARCHAR(20) = NULL;

WHILE LEN(@sample) > 0
BEGIN
    IF PATINDEX('%;%', @sample) > 0
    BEGIN
        SET @individual = SUBSTRING(@sample, 0, PATINDEX('%;%', @sample));

        SELECT  @individual;

        SET @sample = SUBSTRING(@sample, LEN(@individual + ';') + 1, LEN(@sample));
    END;
    ELSE
    BEGIN
        SET @individual = @sample;
        SET @sample = NULL;

        SELECT  @individual;
    END;
END;