I have been frustrated with the 8000 limit set by the varchar data type in MSSQL. For procedures that required text length more than 8000 character limit, MSSQL offers the TEXT datatype. However there are not as many functions that are supported for TEXT datatype. For example if you would like to split the data string which is seperated by a delimitter, then it is not easy to split the text.
The code below can be used to do just that. It really breaks the 8000 character limit
CREATE FUNCTION RowParser
(
@Text TEXT,
@Separator VARCHAR(3) = ','
)
RETURNS TABLE
AS
RETURN
(
SELECT n,
SUBSTRING(@Text collate database_default, n,
CASE SIGN(CHARINDEX(@Separator collate database_default, @Text collate database_default,n)-n)
WHEN -1 THEN
CASE PATINDEX('%,%', SUBSTRING(@Text collate database_default, n, ABS(CHARINDEX(@Separator collate database_default, @Text collate database_default,n)-n)))
WHEN 0 THEN DATALENGTH(@Text)-n+1
ELSE PATINDEX('%,%', SUBSTRING(@Text collate database_default, n, ABS(CHARINDEX(@Separator collate database_default, @Text collate database_default,n)-n))) -1
END
ELSE ABS(CHARINDEX(@Separator collate database_default, @Text collate database_default,n)-n)
END) AS Data
, DATALENGTH(@Text)/n AS Position
, CASE SIGN(CHARINDEX(@Separator collate database_default, @Text collate database_default,n)-n)
WHEN -1 THEN
CASE PATINDEX('%,%', SUBSTRING(@Text collate database_default, n, ABS(CHARINDEX(@Separator collate database_default, @Text collate database_default,n)-n)))
WHEN 0 THEN DATALENGTH(@Text)-n+1
ELSE PATINDEX('%,%', SUBSTRING(@Text collate database_default, n, ABS(CHARINDEX(@Separator collate database_default, @Text collate database_default,n)-n))) -1
END
ELSE ABS(CHARINDEX(@Separator collate database_default, @Text collate database_default,n)-n)
END AS NumberStep
FROM Numbers
WHERE n BETWEEN 0 AND (DATALENGTH(@Text) - DATALENGTH(@Separator))
AND ((SUBSTRING(@Text collate database_default,n-DATALENGTH(@Separator),DATALENGTH(@Separator)) = @Separator AND n > 1) OR n = 1)
)
GO