Removing part of a text field

[katehol]

I'm having trouble removing part of a text field. When I run

SELECT REPLACE(journalarticle.content,'?doAsUserId=BedNOLxX0dg%253D','')
FROM journalarticle 
WHERE content LIKE '%?doAsUserId=BedNOLxX0dg%253D%'

I get the following error

Argument data type text is invalid for argument 1 of replace function.


I understand that this is because the field is of type 'text', but what could I use instead?

Thanks

 

[gmmastros]

What version of SQL Server are you using? If it's SQL2005, then you could use a varchar(max) data type instead.

If you are stuck using SQL2000, then I recommend you do some research on:

TEXTPTR
READTEXT
WRITETEXT
UPDATETEXT

 

[traingamer]

SELECT REPLACE(journalarticle.content,'?doAsUserId=BedNOLxX0dg[b][COLOR=blue]%[/color][/b]253D','')

If the per cent sign (%) is a character in your string, you have to escape it (...dg[%]2...). It you are using it as a wildcard, I don't think you can do a replace that way.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[SQLDenis]

you cannot use replace on text columns

if you are sure the data in the text column is less than 8000 characters, you might be able to convert to varchar(8000) first and then do the replace

However i did not test this since i don't use text datatypes, i watched too many people pull out their hair using it



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[katehol]

Thanks for all the responses.

gmmastros/SQLDenis - I am using SQL 2005 and unfortunately can't change the data type, as it's a pre-designed database. I've literally just been asked to remove the piece of data from a long sting.

traingamer - I'm not using the percentage sign as a wildcard - I also need that removed, as it is part of the string.

So, I'm guessing from all of your responses that I'm not going to be able to remove this string. Maybe I should ask about changing the data type after all...

 

[gmmastros]

>> I am using SQL 2005

Well.... that's good news for you.

You cannot use replace on a text data type, but you can use it on a varchar(max). Since you are using SQL 2005, you can convert your text data to varchar(max), like this...

SELECT REPLACE([!]Convert(varchar(max), [/!]journalarticle.content[!])[/!],'?doAsUserId=BedNOLxX0dg%253D','')
FROM journalarticle
WHERE content LIKE '%?doAsUserId=BedNOLxX0dg%253D%'

 

[katehol]

Thanks gmmastros - worked a treat )