READTEXT Into @localvariable

Hi,
How do I get the results of a READTEXT call into a T-SQL local variable?
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgHi Daniel
"Daniel Jameson" wrote:
> Hi,
> How do I get the results of a READTEXT call into a T-SQL local variable?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
You can't declare a variable as text, therefore (assuming sql 2000) you are
limited to the maximum size of varchar. You can then chunk the text into
sections using substring e.g.
USE TEMPDB
CREATE TABLE MyTextTable ( TxtCol Text )
DECLARE @.ptrval binary(16)
DECLARE @.varchar varchar(8000)
SET @.varchar = REPLICATE('The quick brown fox jumped over the lazy dog', 200
)
INSERT INTO MyTextTable ( TxtCol ) VALUES ( @.varchar )
SELECT DATALENGTH(TxtCol) FROM MyTextTable
SELECT @.ptrval = TEXTPTR(TxtCol) FROM MyTextTable
SELECT LEN(@.varchar)
UPDATETEXT MyTextTable.TxtCol @.ptrval 7964 0 @.varchar
SELECT DATALENGTH(TxtCol) FROM MyTextTable
GO
DECLARE @.str varchar(8000)
DECLARE @.offset int
DECLARE @.maxlength int
SET @.offset = 1
SET @.maxlength = (SELECT DATALENGTH(TxtCol) FROM MyTextTable)
WHILE @.offset < @.maxlength
BEGIN
SELECT @.str = SUBSTRING(TxtCol, @.offset, 8000 ) FROM MyTextTable
SELECT @.str
SET @.offset = @.offset + 8000
END
DROP TABLE MyTextTable
John|||John,
This is what I needed:
SELECT @.str = SUBSTRING(TxtCol, @.offset, 8000 ) FROM MyTextTable
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4466E88C-ACD3-4C6B-88E0-20013BCD26BD@.microsoft.com...
> Hi Daniel
> "Daniel Jameson" wrote:
>> Hi,
>> How do I get the results of a READTEXT call into a T-SQL local variable?
>> --
>> Thank you,
>> Daniel Jameson
>> SQL Server DBA
>> Children's Oncology Group
>> www.childrensoncologygroup.org
> You can't declare a variable as text, therefore (assuming sql 2000) you
> are
> limited to the maximum size of varchar. You can then chunk the text into
> sections using substring e.g.
> USE TEMPDB
> CREATE TABLE MyTextTable ( TxtCol Text )
> DECLARE @.ptrval binary(16)
> DECLARE @.varchar varchar(8000)
> SET @.varchar = REPLICATE('The quick brown fox jumped over the lazy dog',
> 200
> )
> INSERT INTO MyTextTable ( TxtCol ) VALUES ( @.varchar )
> SELECT DATALENGTH(TxtCol) FROM MyTextTable
> SELECT @.ptrval = TEXTPTR(TxtCol) FROM MyTextTable
> SELECT LEN(@.varchar)
> UPDATETEXT MyTextTable.TxtCol @.ptrval 7964 0 @.varchar
> SELECT DATALENGTH(TxtCol) FROM MyTextTable
> GO
> DECLARE @.str varchar(8000)
> DECLARE @.offset int
> DECLARE @.maxlength int
> SET @.offset = 1
> SET @.maxlength = (SELECT DATALENGTH(TxtCol) FROM MyTextTable)
> WHILE @.offset < @.maxlength
> BEGIN
> SELECT @.str = SUBSTRING(TxtCol, @.offset, 8000 ) FROM MyTextTable
> SELECT @.str
> SET @.offset = @.offset + 8000
> END
> DROP TABLE MyTextTable
> John

READTEXT and return values

First question :

How can I set the return value of READTEXT to a variable of type nvarchar.

Second question :

I have a table t1 with a ntext column n1.

The ntext column has has words separated by empty space.
Each word can be assumed to be of size <= 255 characters.

How can I extract all the keywords in the ntext column to a table t2 with a column word nvarchar(255).

Assume that the text in column n1 is big enough so that it cannot be cast into a nvarchar or any other simpler type.

Any help on this is greatly appreciated.
Please do provide a sample code.

Alok.Have you referred to books online for READTEXT topoic.