Query help.

Question

I need to parse the whole string to desire output below.  The sequel statement below is work but I wondered if there are different way
to do it.  I need to insert into temp table below.
Thanks for your help.
---------------------------------------------------
DECLARE @SQLStr VARCHAR(5000)
SET @SQLStr = '4,4,6,6,11,11,23,23,27,27,28,28,29,29,30,30,33,33,37,37,40,40,43,43,45,45,46,46,51,51,58,58,63,63,66,66,68,68,69,69,71,71,76,76,81,81,82,82,83,83,85,85,86,86,87,87,88,88,89,89,90,90,91,91,96,96,99,99,100,100,104,104,105,105,107,107,112,112,118,118,120,120,122,122,127,127,129,129,131,132,132,133,133,136,136,137,137,138,138,149,149,157,157,158,158,160,160,161,161,166,166,167,167,172,172,173,173,174,174,176,176,179,179,181,181,182,182,190,190,191,191,193,193,200,200,202,202,208,208,209,209,210,210,212,216,216,220,220,221,221,223,223,224,224,230,230,231,231,235,235,238,238,239,239,241,241,242,242,244,244,249,249,250,250,251,251,253,253,254,254,257,257,262,262,268,268,272,272,274,274,279,279,280,280,282,282,283,283,284,284,287,287,298,298,302,302,303,303,306,306,309,309,310,313,313,315,316,316,317,317,318,318,320,320,322,322,323,323,325,325,327,327,328,328,329,329,330,330,332,333,333,340,340,341,341,342,342,343,343,345,345,347,347,350,350,352,354,354,362,367,367,372,372,373,373,376,378,379,379,385,403,423,600,998,999,999'
-- Desire results:
--PRINT @SQLStr
SQLStr
-----
100
104
105
107
11
112
118
120
122
...
999
----------------------------------------------------------------------------
-- Testing..
;WITH csvcte (i, j, s)
AS
  ( 
   SELECT i = 1,
          s = CHARINDEX (',', @SQLStr + ','),
          SUBSTRING (@SQLStr, 1, CHARINDEX(',', @SQLStr + ',') - 1 )
   UNION ALL
   SELECT i = (j + 1),
          j = CHARINDEX(',', @SQLStr + ',', (j + 1) ),
          SUBSTRING (@SQLStr, (j + 1), CHARINDEX(',', @SQLStr + ',', (j + 1))  - (j+ 1) )
     FROM csvcte
    WHERE (CHARINDEX ( ',', @SQLStr + ',', (j + 1) ) <> 0)
   )
  SELECT DISTINCT SUBSTRING (@SQLStr, i, (j - i)) AS 'Str'
    FROM csvcte
  ORDER BY 1 ASC
  OPTION (MAXRECURSION 0);
 GO
 
------------------------------------------------------------
DROP TABLE #temp
GO
CREATE TABLE #temp
(
  store_nb  INT NULL
)
  INSERT #temp (store_nb)
  SELECT @SQLStr
  go

Answer 1

One way:

DECLARE @SQLStr VARCHAR(5000)
SET @SQLStr = '4,4,6,6,11,11,23,23,27,27,28,28,29,29,30,30,33,33,37,37,40,40,43,43,45,45,46,46,51,51,58,58,63,63,66,66,68,68,69,69,71,71,76,76,81,81,82,82,83,83,85,85,86,86,87,87,88,88,89,89,90,90,91,91,96,96,99,99,100,100,104,104,105,105,107,107,112,112,118,118,120,120,122,122,127,127,129,129,131,132,132,133,133,136,136,137,137,138,138,149,149,157,157,158,158,160,160,161,161,166,166,167,167,172,172,173,173,174,174,176,176,179,179,181,181,182,182,190,190,191,191,193,193,200,200,202,202,208,208,209,209,210,210,212,216,216,220,220,221,221,223,223,224,224,230,230,231,231,235,235,238,238,239,239,241,241,242,242,244,244,249,249,250,250,251,251,253,253,254,254,257,257,262,262,268,268,272,272,274,274,279,279,280,280,282,282,283,283,284,284,287,287,298,298,302,302,303,303,306,306,309,309,310,313,313,315,316,316,317,317,318,318,320,320,322,322,323,323,325,325,327,327,328,328,329,329,330,330,332,333,333,340,340,341,341,342,342,343,343,345,345,347,347,350,350,352,354,354,362,367,367,372,372,373,373,376,378,379,379,385,403,423,600,998,999,999'

DECLARE  @Split char(1),
  @X xml
 
SET @Split = ','
 
SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@SQLStr,@Split,'</s><s>') + '</s></root>')
 
SELECT [Value] = T.c.value('.','varchar(20)')
FROM @X.nodes('/root/s') T(c)

---

Chuck

Answer 2

This is the best CSV Splitter.

bestCSVsplitter

Vinu Vijayan