Performing String parsing with TEXT DataType

Question

Using TEXT datatype in SQL

Working on SQL 2000, I would like to perform the following

Select @String = SUBSTRING(@String, @idx + DATALENGTH(@firstDelimiter)/ 2,LEN(@String)) 

NOTE: @String is a type TEXT , @FirstDelimiter is of type Varchar(4).

Why am I unable to run the above code? How do I perform the above instead?

Is the above equivalent to this below

SET @String = RIGHT(@String,  LEN(@String) - (@FoundIndex + DATALENGTH(@FirstDelimeter) / 2))

Answer 1

The SQL Server 2000 TEXT data type does not support the usual string manipulation functions. This is just one of the many reasons that in later version of SQL Server, you should switch to VARCHAR(MAX) - that type will support all the usual string functions.

Unfortunately, with SQL Server 2000, you're stuck with the TEXT datatype, and if you use that data type, you're stuck with its very limited set of string functions - see documentation on MSDN here.

As you can see, the SUBSTRING function is supported - but the MSDN docs on that also clearly state that the return type of SUBSTRING is a VARCHAR type - not a TEXT.

String manipulation on SQL Server 2000's TEXT data type is an annoying nightmare - if you have any chance at all, upgrade to 2005 or newer and use VARCHAR(MAX) instead - you'll spare yourself a lot of grief and wasted hours.....