SPLIT a single column value by comma

• sql_learner29

  SSCrazy Eights

  Points: 9107

  More actions

  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: 41690

  More actions

  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

  More actions

  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: 183706

  More actions

  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

  More actions

  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: 183706

  More actions

  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: 41690

  More actions

  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/

  ---