i have string as 'abc,def,ghi,jkl'

Question

i want to string in table column in new row as table

abc
def
ghi
jkl

Answer 1

If you are using SQL SERVER 2016 then use STRING_SPLIT function

SELECT *
FROM   String_split('abc,def,ghi,jkl', ',') 

If you are using anything less than SQL SREVER 2016 then here is the best split string function. Referred from here

CREATE FUNCTION [dbo].[Delimitedsplit8k] (@pString    VARCHAR(8000),
                                          @pDelimiter CHAR(1))
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN 
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
     -- enough to cover NVARCHAR(4000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;

GO

To call the function

SELECT *
FROM   dbo.DelimitedSplit8K('abc,def,ghi,jkl', ',') 

Answer 2

You can use a Recursive CTE to split the string. It will allow you to track the position of the String within the string (if you want) and also provide a link back to your ID/Key value in your master table. (This allows you to avoid using a function if it's not an option)

CREATE TABLE #S(ID INT
                ,Qry VARCHAR(10)
                )

INSERT INTO #S VALUES
(1,'TRA,B')
,(2,'X,YA,ZT') 

DECLARE @s AS VARCHAR(1) = ','

;WITH cte_R
    AS
    (
    SELECT
        ID
        ,1 Ord
        ,LEFT(Qry,CHARINDEX(@s,Qry,0)) Val
        ,RIGHT(Qry,LEN(Qry)-CHARINDEX(@s,Qry,1)) Rem
    FROM
        #S
    WHERE
        Len(Qry) > 0
    UNION ALL
    SELECT
        ID
        ,Ord+1
        ,LEFT(Rem,CHARINDEX(@s,Rem+@s,0)) 
        ,RIGHT(Rem,LEN(Rem)-CHARINDEX(@s,Rem,0))
    FROM
        cte_R
    WHERE
        CHARINDEX(@s,Val,0) > 0
    )
SELECT
    ID
    ,Ord
    ,REPLACE(Val,@s,'') Val
FROM
    cte_R
ORDER BY
    ID
    ,Ord