CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
ihorko
ihorko

Reputation: 6945

MS SQL Server Get value between commas

I have a column in Table1 with string in it separated by commma:

Id Val
1  ,4
2  ,3,1,0
3  NULL
4  ,5,2

Is there a simple way to split and get any value from that column, for example

SELECT Value(1) FROM Table1 should get 

Id Val
1  4
2  3
3  NULL
4  5

SELECT Value(2) FROM Table1 should get 

Id Val
1  NULL
2  1
3  NULL
4  2

Thank you!

Upvotes: 6

Views: 1099

Answers (5)

Soukai
Soukai

Reputation: 463

Here is an example of using a CTE combined with converting the CSV to XML:

DECLARE @Test TABLE (
    CsvData VARCHAR(10)
);

INSERT INTO @Test (CsvData)
VALUES
    ('1,2,3'),
    (',4,5,7'),
    (NULL),
    (',3,');

WITH XmlData AS (
    SELECT CONVERT(XML, '<val>' + REPLACE(CsvData, ',', '</val><val>') + '</val>') [CsvXml]
    FROM @Test
)
SELECT xd.CsvXml.value('val[2]', 'VARCHAR(10)')
FROM XmlData xd;

This would output:

2
4
NULL
3

The column to display is controlled by the XPath query. In this case, val[2].

The main advantage here is that no user-defined functions are required.

Upvotes: 1

M.Ali
M.Ali

Reputation: 69494

Test Data

Declare @t TABLE (Id INT ,  Val VARCHAR(100))
INSERT INTO @t  VALUES
(1  , '4'),
(2  , '3,1,0'),
(3   , NULL),
(4  , '5,2')

Function Definition

CREATE FUNCTION [dbo].[fn_xml_Splitter]
(
      @delimited nvarchar(max)
    , @delimiter nvarchar(1)
    , @Position  INT            = NULL
) 
RETURNS TABLE
AS
RETURN
    (
        SELECT Item
        FROM (
                SELECT   Split.a.value('.', 'VARCHAR(100)') Item
                        , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) ItemNumber
                FROM   
                    (SELECT Cast ('<X>' + Replace(@delimited, @delimiter, '</X><X>') 
                                    + '</X>' AS XML) AS Data
                    ) AS t CROSS APPLY Data.nodes ('/X') AS Split(a) 
            )x
        WHERE x.ItemNumber = @Position OR @Position IS NULL
    );
GO

Function Call

Now you can call this function in two different ways.

1 . to get return an Item on a specific position, specify the position in the 3rd parameter of the function:

SELECT * 
FROM @t t
 CROSS APPLY [dbo].[fn_xml_Splitter](t.Val , ',', 1)

2 . to get return all items, specify the key word DEFUALT in the 3rd parameter of the function:

SELECT * 
FROM @t t
 CROSS APPLY [dbo].[fn_xml_Splitter](t.Val , ',', DEFAULT)

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81930

Another option using a Parse/Split Function and an OUTER APPLY

Example

Declare @YourTable Table ([Id] int,[Val] varchar(50))
Insert Into @YourTable Values 
 (1,',4')
,(2,',3,1,0')
,(3,NULL)
,(4,',5,2')

Select A.ID
      ,Val  =  B.RetVal
 From @YourTable A
 Outer Apply (
                Select * From [dbo].[tvf-Str-Parse](A.Val,',')
                 Where RetSeq = 2
              ) B

Returns

ID  Val
1   4
2   3
3   NULL
4   5

The UDF if Interested

CREATE FUNCTION [dbo].[tvf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);

Upvotes: 2

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Try This Logic Using recursive CTE

DECLARE @Pos INT = 2
DECLARE @T TABLE
(
    Id INT,
    Val VARCHAR(50)
)

INSERT INTO @T
VALUES(1,',4'),(2,',3,1,0'),(3,NULL),(4,',5,2')

;WITH CTE
AS
(
    SELECT
        Id,
        SeqNo = 0,
        MyStr = SUBSTRING(Val,CHARINDEX(',',Val)+1,LEN(Val)),
        Num = REPLACE(SUBSTRING(Val,1,CHARINDEX(',',Val)),',','')
        FROM @T

    UNION ALL

    SELECT
        Id,
        SeqNo = SeqNo+1,
        MyStr = CASE WHEN CHARINDEX(',',MyStr)>0
                    THEN SUBSTRING(MyStr,CHARINDEX(',',MyStr)+1,LEN(MyStr))
                ELSE NULL END,
        Num = CASE WHEN CHARINDEX(',',MyStr)>0
                    THEN REPLACE(SUBSTRING(MyStr,1,CHARINDEX(',',MyStr)),',','')
                ELSE MyStr END
        FROM CTE
            WHERE ISNULL(REPLACE(MyStr,',',''),'')<>''
)   
SELECT
    T.Id,
    CTE.Num
    FROM @T t 
        LEFT JOIN CTE
            ON T.Id = cte.Id
                AND SeqNo = @Pos

My Output for the above

enter image description here

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

Storing comma separated values in a column is always a pain, consider changing your table structure

To get this done, create a split string function. Here is one of the best possible approach to split the string to individual rows. Referred from http://www.sqlservercentral.com/articles/Tally+Table/72993/

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

to call the function 

SELECT *
FROM   yourtable
       CROSS apply (SELECT CASE WHEN LEFT(val, 1) = ',' THEN Stuff(val, 1, 1, '') ELSE val END) cs (cleanedval)
       CROSS apply [dbo].[Delimitedsplit8k](cs.cleanedval, ',')
WHERE  ItemNumber = 1

SELECT *
FROM   yourtable
       CROSS apply (SELECT CASE WHEN LEFT(val, 1) = ',' THEN Stuff(val, 1, 1, '') ELSE val END) cs (cleanedval)
       CROSS apply [dbo].[Delimitedsplit8k](cs.cleanedval, ',')
WHERE  ItemNumber = 2

Upvotes: 2

Related Questions