REPLACE of text field

24 Aug 2006 7:20 PM

David

I am trying to use the following in a SELECT clause as one of the fields and
I get "Argument data type text is invalid for argument 1 of replace
function" but the activity_note is a text data type.
REPLACE(activity_note,' ',CHAR(13)+CHAR(10)) As activity_note

Can anyone help? Thanks.

David

24 Aug 2006 8:10 PM

Jim Underwood

TEXT and NTEXT are special types and do not work like VARCHAR datatypes.
You cannot use many string functions with it, although you can convert it to
VARCHAR and then use string functions on it. Keep in mind that converting
it to VARCHAR will truncate your value if it is longer than your defined
VARCHAR.

Read up on TEXT, NTEXT, and IMAGE datatypes in BOL for more information.

The entry "Managing ntext, text, and image Data" explains some of the
intricacies of working with the long data types.

Show quote

"David" <dlch***@lifetimeinc.com> wrote in message
news:eKT5TL7xGHA.1256@TK2MSFTNGP04.phx.gbl...
> I am trying to use the following in a SELECT clause as one of the fields
and
> I get "Argument data type text is invalid for argument 1 of replace
> function" but the activity_note is a text data type.
> REPLACE(activity_note,' ',CHAR(13)+CHAR(10)) As activity_note
>
> Can anyone help? Thanks.
>
> David
>
>

24 Aug 2006 8:13 PM

David

That is what I thought. Thanks.

David

Show quote

"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:ejYNul7xGHA.1268@TK2MSFTNGP02.phx.gbl...
> TEXT and NTEXT are special types and do not work like VARCHAR datatypes.
> You cannot use many string functions with it, although you can convert it
> to
> VARCHAR and then use string functions on it. Keep in mind that converting
> it to VARCHAR will truncate your value if it is longer than your defined
> VARCHAR.
>
> Read up on TEXT, NTEXT, and IMAGE datatypes in BOL for more information.
>
> The entry "Managing ntext, text, and image Data" explains some of the
> intricacies of working with the long data types.
>
> "David" <dlch***@lifetimeinc.com> wrote in message
> news:eKT5TL7xGHA.1256@TK2MSFTNGP04.phx.gbl...
>> I am trying to use the following in a SELECT clause as one of the fields
> and
>> I get "Argument data type text is invalid for argument 1 of replace
>> function" but the activity_note is a text data type.
>> REPLACE(activity_note,' ',CHAR(13)+CHAR(10)) As activity_note
>>
>> Can anyone help? Thanks.
>>
>> David
>>
>>
>
>

24 Aug 2006 8:16 PM

Hugo Kornelis

On Thu, 24 Aug 2006 14:20:10 -0500, David wrote:

>I am trying to use the following in a SELECT clause as one of the fields and
>I get "Argument data type text is invalid for argument 1 of replace
>function" but the activity_note is a text data type.
>REPLACE(activity_note,' ',CHAR(13)+CHAR(10)) As activity_note
>
>Can anyone help? Thanks.

Hi David,

REPLACE can't be used with datatype text. You could convert to
varchar(8000) first - but that would probably result in data loss. The
alternative is to loop over the text in 8000-byte chunks using READTEXT.
Or upgrade to SQL Server 2005 and convert the column to varchar(MAX).

--
Hugo Kornelis, SQL Server MVP

24 Aug 2006 9:04 PM

Aaron Bertrand [SQL Server MVP]

http://tinyurl.com/fnpqz

(Sorry, the site is down and can only provide a cached version.)

Show quote

"David" <dlch***@lifetimeinc.com> wrote in message
news:eKT5TL7xGHA.1256@TK2MSFTNGP04.phx.gbl...
>I am trying to use the following in a SELECT clause as one of the fields
>and I get "Argument data type text is invalid for argument 1 of replace
>function" but the activity_note is a text data type.
> REPLACE(activity_note,' ',CHAR(13)+CHAR(10)) As activity_note
>
> Can anyone help? Thanks.
>
> David
>
>