Concatenate TEXT Type Value

1kHz · December 30th, 2004, 12:09 AM

Hi..

How do I concatenate TEXT data types? I wanna do something like this:

Code:

DECLARE @myTable TABLE (LongString TEXT)
.
.
UPDATE @myTable SET
    LongString = LongString + 'string to be added'

The '+' operator wont work on TEXT types.
BTW, i'm using MS SQL 2000..

Thank you..

Vadivel · December 30th, 2004, 01:15 AM

First of all you need to use "UpdateText" and not "Update" for Text/nText/Image columns.

Best Regards
Vadivel

MVP ASP/ASP.NET
http://vadivel.thinkingms.com

Vadivel · December 30th, 2004, 01:23 AM

DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(pr_info)
   FROM pub_info pr, publishers p
      WHERE p.pub_id = pr.pub_id
      AND p.pub_name = 'New Moon Books'
UPDATETEXT pub_info.pr_info @ptrval 0 3 'vadi '
GO

here 0 = starting position of the string (its zero based only)
3 = till which position to replace
'vadi ' -- text to be replaced for the characters postion 0-3

Best Regards
Vadivel

MVP ASP/ASP.NET
http://vadivel.thinkingms.com

1kHz · December 30th, 2004, 03:15 AM

Thanks Vadivel..

I've succeeded updating the column with UPDATETEXT.
And I believe the way to read it back is to use READTEXT, but the examples I found is to only read it to certain length.. What should I do to read until the end of the text value?

And also, how can I send the read text to a PRINT statement?

Vadivel · December 31st, 2004, 01:12 AM

You can also use normal "Select" statement to fetcht the value of TEXT datatype. But always the TEXT column should be the last one in the list.

i.e., Select col1, col2, TextColumn from Table1

here TextColumn -- is a column of TEXT datatype. Hope this helps!

Best Regards
Vadivel

MVP ASP/ASP.NET
http://vadivel.thinkingms.com

Vadivel · December 31st, 2004, 01:29 AM

Btw, uou can make use of datalength() to find the length of data in text column.

Best Regards
Vadivel

MVP ASP/ASP.NET
http://vadivel.thinkingms.com