Link to home
Start Free TrialLog in
Avatar of MichaelMullin
MichaelMullin

asked on

Replace carriage return with ' '

I have a field in table Appointment called Memo (type text)
I would like a sql script which will remove all carriage returns in the text of the memo field and replace with a space or colon or semi-colon
I have tried:
update appointment
set memo = replace(memo,char(13),' ')
but receive error:
Argument data type text is invalid for argument 1 of replace function.
using: set memo = replace('memo',char(13),' ') simply replaces the memo with the word 'memo'
Thanks for any help,
Michael
Avatar of arbert
arbert

YOu can't use replace with a text column...You need to use UpdateText--it's not very pretty...
Avatar of Lowfatspread
nice link arbert

of course if the memo is actually less than 8000 characters

you could do

update appointment
set memo = replace(convert(varchar(8000),memo,char(13),' ')
where Datalength(memo) <=8000
and patindex('%' + char(13) + '%' , memo) > 0

but you would want a where clause anyway to stop unnecessary updates..
Of course, if you're using less than 8000 chars, we would hope you wouldn't be using a text type in the first place :)  They get difficult to work with....
use substring function

-- cheers
For grins and giggles, try

set memo = replace(memo,char(10),' ')

I have had that work once before when char(13) would not.

Incidentally, what results are you getting now?
ASKER CERTIFIED SOLUTION
Avatar of arbert
arbert

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
There's a much easier solution; REPLACE will work if you update against the string of the text column:

UPDATE Appointment
   SET Memo = REPLACE(SUBSTRING(Memo, 1, DATALENGTH(Memo)), char(13), ';')
   WHERE charindex(char(13) , Memo) > 0