TSQL Parse String with 4 delimiters

Question

I am trying to parse this string :

'200|50|jo.th@xxx.com|09\23\2016|07:00:00'

into 5 columns and I am getting frustrated.

The delimiter is pipe |

The fields are not fixed so I need to use charindex in order to find the location of the delimiter ?

Please help Thanks

Probably you need something like this: http://stackoverflow.com/questions/10914576/t-sql-split-string — Massimo Della Calce, Nov 15 '16 at 11:53
Rich thanks This looks great but how do i use this inside a query from a table that contains this field to be parsed ? — Barakula, Nov 15 '16 at 14:12

John Cappelletti · Accepted Answer · 2016-11-15T12:46:59.013

Another option is as follows. This can be baked into a TVF or even a Cross Apply

Declare @String varchar(max) = '200|50|jo.th@xxx.com|09\23\2016|07:00:00'

Select Pos1 = xDim.value('/x[1]','varchar(max)')
      ,Pos2 = xDim.value('/x[2]','varchar(max)')
      ,Pos3 = xDim.value('/x[3]','varchar(max)')
      ,Pos4 = xDim.value('/x[4]','varchar(max)')
      ,Pos5 = xDim.value('/x[5]','varchar(max)')
      ,Pos6 = xDim.value('/x[6]','varchar(max)')
      ,Pos7 = xDim.value('/x[7]','varchar(max)')
      ,Pos8 = xDim.value('/x[8]','varchar(max)')
      ,Pos9 = xDim.value('/x[9]','varchar(max)')
 From (Select Cast('<x>' + Replace(@String,'|','</x><x>')+'</x>' as XML) as xDim) A

Returns

Edit - To Use in a Cross Apply - Easy to expand/contract

Declare @YourTable table (ID int,SomeString varchar(max))
Insert Into @YourTable values
(1,'200|50|jo.th@xxx.com|09\23\2016|07:00:00'),
(2,'400|99|james.th@xxx.com|11\15\2016|09:00:00')

Select A.ID
      ,B.*
 From  @YourTable A
 Cross Apply (
                Select Pos1 = xDim.value('/x[1]','varchar(max)')
                      ,Pos2 = xDim.value('/x[2]','varchar(max)')
                      ,Pos3 = xDim.value('/x[3]','varchar(max)')
                      ,Pos4 = xDim.value('/x[4]','varchar(max)')
                      ,Pos5 = xDim.value('/x[5]','varchar(max)')
                      ,Pos6 = xDim.value('/x[6]','varchar(max)')
                      ,Pos7 = xDim.value('/x[7]','varchar(max)')
                      ,Pos8 = xDim.value('/x[8]','varchar(max)')
                      ,Pos9 = xDim.value('/x[9]','varchar(max)')
                 From (Select Cast('<x>' + Replace(A.SomeString,'|','</x><x>')+'</x>' as XML) as xDim) A
             ) B

Returns

@Barakula Happy to help. – John Cappelletti Nov 15 '16 at 14:22 — John Cappelletti, Nov 15 '16 at 14:22

Rich Benner · Answer 2 · 2016-11-15T12:31:00.900

So first I'd locate the position of each delimiter and then use this to split your string. Something like this;

DECLARE @TextString nvarchar(100); SET @TextString = '200|50|jo.th@xxx.com|09\23\2016|07:00:00'
DECLARE @FirstDelimiter int
DECLARE @SecondDelimiter int
DECLARE @ThirdDelimiter int
DECLARE @FourthDelimiter int

SET @FirstDelimiter = CHARINDEX('|',@TextString)
SET @SecondDelimiter =  CHARINDEX('|',@TextString,@FirstDelimiter+1)
SET @ThirdDelimiter =  CHARINDEX('|',@TextString,@SecondDelimiter+1)
SET @FourthDelimiter =  CHARINDEX('|',@TextString,@ThirdDelimiter+1)

Final Query;

SELECT
@TextString Main_String
,SUBSTRING(@TextString,1,@FirstDelimiter-1) First_String
,SUBSTRING(@TextString,@FirstDelimiter+1,(@SecondDelimiter-@FirstDelimiter)-1) Second_String
,SUBSTRING(@TextString,@SecondDelimiter+1,(@ThirdDelimiter-@SecondDelimiter)-1) Third_String
,SUBSTRING(@TextString,@ThirdDelimiter+1,(@FourthDelimiter-@ThirdDelimiter)-1) Fourth_String
,SUBSTRING(@TextString,@FourthDelimiter+1,LEN(@TextString)-@FourthDelimiter) Fifth_String

Result Set;

Main_String                                 First_String    Second_String   Third_String    Fourth_String   Fifth_String
200|50|jo.th@xxx.com|09\23\2016|07:00:00    200             50              jo.th@xxx.com   09\23\2016      07:00:00

score 0 · Answer 3 · answered Nov 15 '16 at 12:20

I use the following to get delimnated values into a set of tables rows (usually for passing in multiple values in a stored procedure parameter which can then be joined on), pivoting can transform it into columns.

(Compatible with SQL Server 2005 and later)

DECLARE @Input varchar(MAX)
DECLARE @Delim char(1)

SET @Input = '200|50|jo.th@xxx.com|09\23\2016|07:00:00' 
SET @Delim = '|'

DECLARE @VALUES TABLE (ColNo INT, [Param] varchar(MAX))

DECLARE @ChrInd INT
DECLARE @Piece varchar(MAX)
DECLARE @ColNo INT
SET @ColNo = 1
SELECT @ChrInd = 1

WHILE @ChrInd > 0
    BEGIN
        SELECT @ChrInd = CHARINDEX(@Delim,@Input)

        IF @ChrInd > 0
            SELECT @Piece = LEFT(@Input,@ChrInd - 1)
        ELSE
            SELECT @Piece = @Input

        INSERT INTO @VALUES(ColNo, [Param]) VALUES(@ColNo, @Piece)
        SELECT @Input = RIGHT(@Input,LEN(@Input) - @ChrInd)

        IF LEN(@Input) = 0 BREAK

        SET @ColNo = @ColNo + 1 
    END

--SELECT AS ROWS
SELECT * FROM @VALUES 

--SELECT AS COLUMNS
SELECT * FROM
(
    SELECT ColNo, [Param] FROM @VALUES
) src
PIVOT(
    MAX([Param])
    FOR ColNo IN ([1],[2],[3],[4],[5])
) AS pvt

Results

ColNo       Param
--------------------------------------------------------------------------
1           200
2           50
3           jo.th@xxx.com
4           09\23\2016
5           07:00:00


1         2         3               4           5         
--------- --------- --------------- ----------- ----------
200       50        jo.th@xxx.com   09\23\2016   07:00:00

TSQL Parse String with 4 delimiters

3 Answers3

Linked