Create a function to split delimited string into a table

Question

In order to split delimited string, I would need the creation of a function returning a table and taking a comma separated string as parameter.

For instance, have a look at this input table:

Using the function, the result would be:

In this case, the delimited string is the Person column that should be taken as parameter.

Thanks in advance.

http://stackoverflow.com/questions/2696884/split-value-from-one-field-to-two — safin chacko, Nov 10 '16 at 08:16
after posting questions u must check answers and it is satisfies your requirement u have to accept ..@pravesh.kan — Chanukya, Nov 16 '16 at 05:34

Shakeer Mirza · Answer 1 · 2016-11-11T07:56:13.593

Try this one

    --Build Schema for Your Case

    DECLARE @TAB TABLE (ID INT, NAME VARCHAR(1000))
    INSERT INTO @TAB
    SELECT 1,'Pravesh,Simon'
    UNION ALL
    SELECT 2,'Me,Myself,I'


    --Used Recursive CTE to achieve Tabular view of Comma Separated Column
    --Used While loop to take next row
    --Used Table Variable @RESULT to store your Result Set

    DECLARE @ID INT,  @T VARCHAR(100), @SNO INT= 1, @MAX_SNO INT
    DECLARE @RESULT TABLE (ID INT, NAME VARCHAR(1000))


    SELECT @SNO=1, @MAX_SNO =MAX(ID) FROM @TAB

    WHILE(@SNO<=@MAX_SNO)
    BEGIN
    SELECT @ID = ID, @T = NAME FROM @TAB WHERE ID=@SNO
    SET @T =@T+','


    ;WITH MyCTE(Start,[End]) AS(

    SELECT 1 AS Start,CHARINDEX(',',@T,1) AS [End]
    UNION ALL
    SELECT [End]+1 AS Start,CHARINDEX(',',@T,[End]+1)AS [End] 
    from MyCTE where [End]<LEN(@T)
    )

    INSERT INTO @RESULT

    Select @SNO AS ID, SUBSTRING(@T,Start,[End]-Start) NAME from MyCTE;


    SET @SNO+=1
    END

    --Getting Result Set

    SELECT * FROM @RESULT

Edit: From Your Comments

If you want to do the above with a function, this one far easy than above looping.

    CREATE FUNCTION [DBO].[FN_SPLIT_STR_TO_COL] (@T AS VARCHAR(4000) )
    RETURNS
     @RESULT TABLE(VALUE VARCHAR(250))
    AS
    BEGIN
         SET @T= @T+','
           ;WITH MYCTE(START,[END]) AS(

        SELECT 1 AS START,CHARINDEX(',',@T,1) AS [END]
        UNION ALL
        SELECT [END]+1 AS START,CHARINDEX(',',@T,[END]+1)AS [END] 
        FROM MYCTE WHERE [END]<LEN(@T)
        )
        INSERT INTO @RESULT 
        SELECT SUBSTRING(@T,START,[END]-START) NAME FROM MYCTE;

          RETURN 
    END

Now just call the function for every row by passing column to it.

With Cross Apply

SELECT ID,FN_RS.VALUE FROM @TAB
CROSS APPLY
(SELECT * FROM [DBO].[FN_SPLIT_STR_TO_COL] (NAME)) AS FN_RS

Your Query is good but I'm looking for a function. Can you help? — Pravesh.Kan, Nov 10 '16 at 08:38
can u check my answer that will fulfill your conditions...@Pravesh.Kan — Chanukya, Nov 11 '16 at 04:47

safin chacko · Answer 2 · 2016-11-10T08:24:59.783

TRY THIS QUERY

//Which will work only when having 3 comma only


        SELECT Id,SUBSTRING_INDEX(SUBSTRING_INDEX(`Person`, ',', 1), ',', -1)  as Person
          FROM `table`
         UNION
         select Id,SUBSTRING_INDEX(SUBSTRING_INDEX(`Person`, ',', 2), ',', -1)  as Person
          FROM `table`
        UNION
         select Id,SUBSTRING_INDEX(SUBSTRING_INDEX(`Person`, ',', 3), ',', -1)  as Person
          FROM `table`
    order by Id ASC

score 0 · Answer 3 · answered Nov 10 '16 at 09:22

create function [dbo].[udf_splitstring] (@tokens    varchar(max),
                                         @delimiter varchar(5))
returns @split table (
  token varchar(200) not null )
as
  begin
      declare @list xml

      select @list = cast('<a>'
                          + replace(@tokens, @delimiter, '</a><a>')
                          + '</a>' as xml)

      insert into @split
                  (token)
      select ltrim(t.value('.', 'varchar(200)')) as data
      from   @list.nodes('/a') as x(t)

      return
  end


  create table #dup
( id int, name varchar(100)
)
insert into #dup values
(1,'leela,venkatesh,don'),
(2,'john,smith,lewis')

select id,b.token from #dup
cross apply 
(select token from  udf_splitstring(name,',') )b

Create a function to split delimited string into a table

3 Answers3