T-SQL split string

Question

I have a SQL Server 2008 R2 column containing a string that I need to split by a comma. I have seen many answers on StackOverflow but none of them works in R2. I have made sure I have select permissions on any split function examples. Any help greatly appreciated.

Answer 1

You can use this SQL :

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )

RETURNS

 @returnList TABLE ([Name] [nvarchar] (500))

AS

BEGIN

 DECLARE @name NVARCHAR(255)

 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0

 BEGIN

  SELECT @pos  = CHARINDEX(',', @stringToSplit)  

  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 

  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)

 END

INSERT INTO @returnList

 SELECT @stringToSplit

 RETURN

END

For using this:-

SELECT * FROM dbo.splitstring('91,12,65,78,56,789')

 )

SELECT PostId, LastDate

FROM PostCTE

WHERE RowNumber > @Start AND RowNumber <= @End

ORDER BY PostId