Split String Via Select Statement

Fairly simple question, but seems impossibe to get an answer. If I have this: declare @Agent nvarchar(4000) = '2131235,334225'; Is there a select statement I can write to split t

Solution 1:

DECLARE@LeftPartTABLE
(Agent int
)

DECLARE@RightPartTABLE
(Agent1 int
)

declare@Agent nvarchar(4000) ='2131235,334225';

INSERTINTO@LeftPartSELECTLEFT(@Agent,7)

INSERTINTO@RightPartSELECTRIGHT(@Agent,6)

SELECT*INTO #Temp
FROM 
(
SELECT*FROM@LeftPartUNIONALLSELECT*FROM@RightPart
)A

SELECT*FROM #Temp

Solution 2:

How about:

DECLARE@AgentVARCHAR(4000) ='2131235,334225'DECLARE@DelimiterVARCHAR(1) =','

;WITH CTE
AS
(
    SELECT1AS ID 
    UNIONALLSELECT ID +1FROM CTE
    WHERE ID <4000
)   

SELECTSUBSTRING(@Agent, t.ID, CHARINDEX(@Delimiter, @Agent+@Delimiter, t.ID) - t.ID) AS Agent
FROM CTE t
WHERE t.ID <= DATALENGTH(@Agent)+1ANDSUBSTRING(@Delimiter+@Agent, t.ID, 1) =@Delimiter
OPTION (MAXRECURSION 4000)

More information can be found here: http://www.sqlservercentral.com/articles/Tally+Table/72993/