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))

You realize that @firstDelimiter, nor DATALENGTH of, will give you a position in @String? At most, the result will be four if @firstDelimiter is VARCHAR(4) before the division... — OMG Ponies
– OMG Ponies, Commented Oct 31, 2010 at 19:07
Can you describe exactly what you want to do? SUBSTRING will always return varchar (upto to 8000) of course, so LEN(@String) is moot if > 8000. — gbn
– gbn, Commented Oct 31, 2010 at 19:18
I am here trying to run a SUBSTRING routine supplying it with TEXT to potentially return TEXT. If I am unable to use SUBSTRING for this, what other way can I accomplish same? — Kobojunkie
– Kobojunkie, Commented Oct 31, 2010 at 20:00

marc_s · Accepted Answer · 2010-10-31 21:30:48Z

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.....

Collectives™ on Stack Overflow

Performing String parsing with TEXT DataType

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related