Programming Tutorials

TEXT datatype SPLIT in MSSQL - to solve the 8000 limit set by varchar

By: Dave in JDBC Tutorials on 2010-04-06  

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