TEXT datatype SPLIT in MSSQL - to solve the 8000 limit set by varchar
By: Dave
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
Archived Comments
1. 'Numbers' is the name of his table, you need to substitute with the name of your table's name instea
View Tutorial By: Amir at 2010-09-15 10:59:20
2. HI,
The function is throwing an error saying that "Invalid object name 'Numbers'
View Tutorial By: Vamshi at 2010-08-31 05:04:03
Comment on this tutorial
- Data Science
- Android
- AJAX
- ASP.net
- C
- C++
- C#
- Cocoa
- Cloud Computing
- HTML5
- Java
- Javascript
- JSF
- JSP
- J2ME
- Java Beans
- EJB
- JDBC
- Linux
- Mac OS X
- iPhone
- MySQL
- Office 365
- Perl
- PHP
- Python
- Ruby
- VB.net
- Hibernate
- Struts
- SAP
- Trends
- Tech Reviews
- WebServices
- XML
- Certification
- Interview
categories
Related Tutorials
TEXT datatype SPLIT in MSSQL - to solve the 8000 limit set by varchar
What is Referential Integrity in databases?
Handling CSV in Stored Procedures
java.lang.NoClassDefFoundError and java.lang.NoSuchMethodError
Calling a Stored Procedure from JDBC in Java
setSavepoint and releaseSavepoint Example in Java
PreparedStatement Example in Java
Creating Database Tables Using ANT
Using the DriverManager Class vs Using a DataSource Object for a connection
Stored Procedures example in SQL