Split comma separated string in sql server

 

DECLARE @Num INT,@Pos INT, @NextPos INT, @ToProfileIds NVARCHAR(MAX)
SET @ToProfileIds = ‘sainath,sagar,gopi,ad’
SET @Pos = 1
WHILE(@Pos <= LEN(@ToProfileIds))
BEGIN
SELECT @NextPos = CHARINDEX(N’,’, @ToProfileIds, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@ToProfileIds) + 1
insert into #tmptable
SELECT RTRIM(LTRIM(SUBSTRING(@ToProfileIds, @Pos, @NextPos – @Pos)))
SELECT @Pos = @NextPos+1
END