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

Previous
<< What is Referential Integrity in databases?

Next
Import TEXT to TABLE in MSSQL>>

(追記) (追記ここまで)