How to get the element from a delimited string by its index?

s.dragos Published at Dev

s.dragos

I have this function (credit: searchsqlserver):

CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000)             -- List of delimited items
    , @sDelimiter VARCHAR(8000) = ','     -- delimiter that separates items
 ) RETURNS @List TABLE (item VARCHAR(8000))
     BEGIN
     DECLARE @sItem VARCHAR(8000)
     WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
         BEGIN
         SELECT
         @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,
            @sInputList,0)-1))),
         @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,
            @sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
         IF LEN(@sItem) > 0
            INSERT INTO @List SELECT @sItem
         END

     IF LEN(@sInputList)> 0
     INSERT INTO @List SELECT @sInputList -- Put the last item in
     RETURN
     END
 GO

It takes as parameters a string and a delimiter and returns the delimited elements one by one.

select * from fnSplit('1,22,333', ',')    --   returns 1 22 333

I'll confess that I`m new to SQL and I simply can't follow the whole logic behind this function. What I'm trying to achieve is a function that has a third parameter(an index) and returns the element on the position mention by the index. For example:

select * from fnSplit('1 22 333 444 5555 666', ' ' , 2 ) --  333

select * from fnSplit('1 22 333 444 5555 666', ' ' , 0 ) --  1

Lukasz Szozda

Using multistatment table-valued UDF with loop to parse string is very inefficient. Better approaches: Split strings the right way – or the next best way

Anyway if you want to adapt your function you could set IDENTITY column for table variable and then filter based on third parameter:

CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000)             -- List of delimited items
    , @sDelimiter VARCHAR(8000) = ','     -- delimiter that separates items
    ,@num INT
 ) RETURNS @List TABLE ( item VARCHAR(8000))
     BEGIN
     DECLARE @ListHelper AS TABLE(id INT IDENTITY(1,1), item VARCHAR(8000));
     DECLARE @sItem VARCHAR(8000)
     WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
         BEGIN
         SELECT
         @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,
            @sInputList,0)-1))),
         @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,
            @sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
         IF LEN(@sItem) > 0
            INSERT INTO @ListHelper SELECT @sItem
         END

     IF LEN(@sInputList)> 0
     INSERT INTO @ListHelper SELECT @sInputList -- Put the last item in
     INSERT INTO @List
     SELECT item
     FROM @ListHelper
     WHERE id = @num
     RETURN
     END
 GO

select * from fnSplit('1 22 333 444 5555 666', ' ' , 3 );
--333

LiveDemo

Collected from the Internet

Please contact [email protected] to delete if infringement.