Post reply

SPLIT a single column value by comma

  • sql_learner29

    SSCrazy Eights

    Points: 9107

    October 15, 2013 at 8:22 am

    #299983

    Hi

    I have a query that returns a column whose value I have to use in an IN clause separated by commas

    for example in the below code I want to use like this where MYcolumnName IN( 2,5,6)

    here I have 256 in NAME and I have separated it by commas...

    CREATE TABLE #temp

    ( ID int,NAME char(10))

    INSERT INTO #temp

    SELECT 1,'256'

    union all

    SELECT 2,'56'

    union all

    SELECT 3,'2GH6'

    union all

    SELECT 4,'256W'

    union all

    SELECT 5,'256GWP'

    union all

    SELECT 6,'256'

    SELECT * from #temp

    Any help on this please..

    I can't use PARSENAME function as its valid only for 4 CHAR but my string is 10 char long...

    Thanks

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • Cadavre

    SSC-Forever

    Points: 41689

    October 15, 2013 at 8:28 am

    #1658552

    Take a look at the 8K Splitter[/url].

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • sql_learner29

    SSCrazy Eights

    Points: 9107

    October 15, 2013 at 8:36 am

    #1658557

    Thanks a lot..

    but If I had to add CTE and temp table I could have done this ,Can you suggest any simple way to just add a third column and show split values separated by comma for second column in that...

    Thanks

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • Luis Cazares

    SSC Guru

    Points: 183633

    October 15, 2013 at 8:44 am

    #1658561

    Not exactly what you might be looking for, but instead of trying to add commas after each character, you can use a subquery in the IN clause. Something like this:

    WITH Tally(n) AS(

    SELECT *

    FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))x(n))

    SELECT NAME, SUBSTRING( NAME, n, 1)

    from #temp te

    JOIN Tally ta ON LEN( te.NAME) >= ta.n

    Of course, the CTE can´t be in the subquery but it should be on the outer query.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • sql_learner29

    SSCrazy Eights

    Points: 9107

    October 15, 2013 at 8:55 am

    #1658565

    Thank You Sir Luis ..

    Your code works ...I did implement it ...

    Thanks

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • Luis Cazares

    SSC Guru

    Points: 183633

    October 15, 2013 at 9:06 am

    #1658569

    I'm glad that I could be of help. It's weird to be called Sir Luis, makes me feel like someone important. 😀

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • Cadavre

    SSC-Forever

    Points: 41689

    October 15, 2013 at 9:20 am

    #1658579

    Learner1 (10/15/2013)

    Thanks a lot..

    but If I had to add CTE and temp table I could have done this ,Can you suggest any simple way to just add a third column and show split values separated by comma for second column in that...

    Thanks

    Teach a man to fish. . .

    This is what I'd have done to the 8K splitter: -

    CREATE FUNCTION [dbo].[DelimitedSplit8K] (@pString VARCHAR(8000))

    --WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!

    RETURNS TABLE WITH SCHEMABINDING AS RETURN

    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),

    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 (SELECT TOP (ISNULL(LEN(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM E4),

    cteStart(N1) AS (SELECT t.N

    FROM cteTally t),

    cteLen(N1,L1) AS(SELECT s.N1, 1

    FROM cteStart s)

    SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),

    Item = SUBSTRING(@pString, l.N1, l.L1)

    FROM cteLen l;

    Here's how you'd use it on your sample data: -

    SELECT NAME, Item

    FROM #temp

    CROSS APPLY [dbo].[DelimitedSplit8K](NAME);

    Here's the returned values: -

    NAME Item

    ---------- -----

    256 2

    256 5

    256 6

    56 5

    56 6

    2GH6 2

    2GH6 G

    2GH6 H

    2GH6 6

    256W 2

    256W 5

    256W 6

    256W W

    256GWP 2

    256GWP 5

    256GWP 6

    256GWP G

    256GWP W

    256GWP P

    256 2

    256 5

    256 6

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply