Split a comma separated string without looping ( No while loops )

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  June 21, 2018 at 10:02 am

  #363322

  
  Question: I am sure I can write a user defined function  and go through a lop to break each item and place in a table.
  Is there a super easy way to do this  ( Now C# language has a string.split() function that will throw the values to an array, that is cool ) 

  
Declare @PlanCodes VARCHAR(500)
Select @PlanCodes='MMP,MMS,CTP'

  In the mean time I will also do some research and see whether I can find one.

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  June 21, 2018 at 10:04 am

  #1994858

  mw_sql_developer - Thursday, June 21, 2018 10:02 AM

  
  Question: I am sure I can write a user defined function  and go through a lop to break each item and place in a table.
  Is there a super easy way to do this  ( Now C# language has a string.split() function that will throw the values to an array, that is cool ) 

  
Declare @PlanCodes VARCHAR(500)
Select @PlanCodes='MMP,MMS,CTP'

  In the mean time I will also do some research and see whether I can find one.

  OOps sorry, my pal here just found out it could be done. So lets close the case.

  Declare @PlanCodes VARCHAR(500)
Select @PlanCodes='MMP,MMS,CTP'
------------------------------------------------------
;

      --We need this to split the string that has multiple values sent by the user in the @PlanCodes varuable
    --This variable will have a comma separated list of Plans
  WITH CTE_String_Pieces
  AS
 (
  SELECT
    1 AS ID
    ,1 AS StartString
    ,CHARINDEX(',', @PlanCodes) AS StopString   UNION ALL

     SELECT
    ID + 1
    ,StopString + 1
    ,CHARINDEX(',', @PlanCodes, StopString + 1)
   FROM
    CTE_String_Pieces
   WHERE
    StopString > 0
  )
  ,
  CTE_String_Split
  AS
  (
   SELECT
    CONVERT(nvarchar,SUBSTRING(@PlanCodes, StartString,
            CASE
             WHEN StopString > 0 THEN StopString - StartString
             ELSE LEN(@PlanCodes)
             END
            )
       ) AS PlanCode
   FROM
    CTE_String_Pieces
  )
    Select * INTO #UserSelectedPlanCodes FROM CTE_String_Split;

      Select * FROM #UserSelectedPlanCodes
------------------------------------------------------

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 21, 2018 at 10:18 am

  #1994862

  Check out this article: http://www.sqlservercentral.com/articles/Tally+Table/72993/

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  June 21, 2018 at 3:10 pm

  #1994921

  mw_sql_developer - Thursday, June 21, 2018 10:04 AM

  mw_sql_developer - Thursday, June 21, 2018 10:02 AM

  
  Question: I am sure I can write a user defined function  and go through a lop to break each item and place in a table.
  Is there a super easy way to do this  ( Now C# language has a string.split() function that will throw the values to an array, that is cool ) 

  
Declare @PlanCodes VARCHAR(500)
Select @PlanCodes='MMP,MMS,CTP'

  In the mean time I will also do some research and see whether I can find one.

  OOps sorry, my pal here just found out it could be done. So lets close the case.

  Declare @PlanCodes VARCHAR(500)
Select @PlanCodes='MMP,MMS,CTP'
------------------------------------------------------
;

      --We need this to split the string that has multiple values sent by the user in the @PlanCodes varuable
    --This variable will have a comma separated list of Plans
  WITH CTE_String_Pieces
  AS
 (
  SELECT
    1 AS ID
    ,1 AS StartString
    ,CHARINDEX(',', @PlanCodes) AS StopString   UNION ALL

     SELECT
    ID + 1
    ,StopString + 1
    ,CHARINDEX(',', @PlanCodes, StopString + 1)
   FROM
    CTE_String_Pieces
   WHERE
    StopString > 0
  )
  ,
  CTE_String_Split
  AS
  (
   SELECT
    CONVERT(nvarchar,SUBSTRING(@PlanCodes, StartString,
            CASE
             WHEN StopString > 0 THEN StopString - StartString
             ELSE LEN(@PlanCodes)
             END
            )
       ) AS PlanCode
   FROM
    CTE_String_Pieces
  )
    Select * INTO #UserSelectedPlanCodes FROM CTE_String_Split;

      Select * FROM #UserSelectedPlanCodes
------------------------------------------------------

  That uses a recursive CTE to count.  Please see the following article why that's actually a pretty nasty form of "Hidden RBAR".  A well written WHILE loop would actually perform better.
  http://www.sqlservercentral.com/articles/T-SQL/74118/

  The link that Lynn pointed you to would be the way to go for this type of thing.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)