No Functions, No Split_string. Use Xml To Split String Into Different Fields By Delimiter

I have a string like this that doesn't have a consistent format and needs to be broken into fields by the ':' delimiter 1:35410:102001001:102001:10:1 'STRING_SPLIT' is not a rec

Solution 1:

here is an example of a TVF that I use quite often. What it will do is take in a delimited string value and the value of the delimiter and return a table containing the split values.

CREATEFUNCTION [dbo].[UDF_GetTableFromList]
(
    @LISTvarchar(max), 
    @DELIMITERchar(1)=','
)

RETURNS@RETURN_TABLE TABLE (Param varchar(4000))

ASBEGINDeclare@POSint,
    @PIECEvarchar(4000)

Set@LIST= ltrim(rtrim(@LIST)) +@DELIMITERSet@POS= charindex(@DELIMITER, @LIST, 1)

-- parse the string into a table
if REPLACE(@LIST, @DELIMITER, '') <>''begin

WHILE @POS>0beginSET@PIECE= LTRIM(RTRIM(LEFT(@LIST, @POS-1)))
    IF @PIECE<>''beginINSERTINTO@RETURN_TABLE (param) VALUES (CAST(@PIECEASvarchar(4000)))

        endSET@LIST=RIGHT(@LIST, LEN(@LIST) -@POS)
    SET@POS= CHARINDEX(@DELIMITER, @LIST, 1)

    ENDEndRETURNEND
GO

execution example would be

select * from [dbo].[UDF_GetTableFromList]('a,b,c,d',',')

and that would yield

in your particular case it would look something like this

select * from [dbo].[UDF_GetTableFromList](' 1:35410:102001001:102001:10:1',':')

and yield the following