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
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
YOu can't use replace with a text column...You need to use UpdateText--it's not very pretty...
Previous thread with answer:
https://www.experts-exchange.com/questions/20182000/how-to-replace-carriage-return-with-BR-for-a-text-data-type.html
Brett
https://www.experts-exchange.com/questions/20182000/how-to-replace-carriage-return-with-BR-for-a-text-data-type.html
Brett
nice link arbert
of course if the memo is actually less than 8000 characters
you could do
update appointment
set memo = replace(convert(varchar(80 00),memo,c har(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 the memo is actually less than 8000 characters
you could do
update appointment
set memo = replace(convert(varchar(80
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
-- 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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
UPDATE Appointment
SET Memo = REPLACE(SUBSTRING(Memo, 1, DATALENGTH(Memo)), char(13), ';')
WHERE charindex(char(13) , Memo) > 0