mardi 5 mai 2015

Split Number string into temp table in SQL server

I want to split string like this '1,2,3,4,5,6,7,8,9,10,11,12'

I found code in Internet and I modifier to my work

DECLARE @inpMonth NVARCHAR(MAX) = '1,2,3,4,5,6,7,8,9,10,11,12'
DECLARE @lastYear INT =  2010
DECLARE @delimeter NVARCHAR(1) = ',' 
DECLARE @mname NVARCHAR(30)
CREATE TABLE #tmp2 (label INT,yy NVARCHAR(100),fromMonth INT,toMonth INT,link INT,mName NVARCHAR(30)) 

WHILE LEN(@inpMonth) > 0 
BEGIN 
    DECLARE @TYear NVARCHAR(100)
    DECLARE @chidx BIGINT = CHARINDEX(@delimeter,@inpMonth)

    IF CHARINDEX(@delimeter,@inpMonth) > 0
    BEGIN
        SET  @TYear = SUBSTRING(@inpMonth,0,CHARINDEX(@delimeter,@inpMonth,0))
    END
    ELSE 
        BEGIN 
        SET  @TYear = @inpMonth
        SET @inpMonth = '' 
        END

    SET @mname = CASE @TYear 
                    WHEN '1' THEN 'Jan' 
                    WHEN '2' THEN 'Feb' 
                    WHEN '3' THEN 'Mar' 
                    WHEN '4' THEN 'Apr' 
                    WHEN '5' THEN 'May' 
                    WHEN '6' THEN 'Jun' 
                    WHEN '7' THEN 'Jul' 
                    WHEN '8' THEN 'Aug' 
                    WHEN '9' THEN 'Sep' 
                    WHEN '10' THEN 'Oct' 
                    WHEN '11' THEN 'Sep' 
                    WHEN '12' THEN 'Dec' 
                END 
    INSERT INTO  #tmp2 VALUES (@lastYear + 543, @lastYear,@TYear,@TYear, 1,@mname)

    SET @inpMonth = REPLACE(@inpMonth,@TYear + @delimeter , '')

END 

SELECT *
FROM #tmp2

DROP TABLE #tmp2 

Then when I run this code I get record 112 instead 11 and 12 because at end of while statement

REPLACE() are replace '1,' (in first loop) in @inpMonth Like this

REPLACE('1,2,3,4,5,6,7,8,9,10,11,12','1,','')

I get this Result 2,3,4,5,6,7,8,9,10,112

How to I get correct like this '2,3,4,5,6,7,8,9,10,11,12'

Thank you !

Aucun commentaire:

Enregistrer un commentaire