?
Solved

Replace carriage return with ' '

Posted on 2004-08-07
8
Medium Priority
?
24,768 Views
Last Modified: 2011-10-03
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
0
Comment
Question by:MichaelMullin
8 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 11743772
YOu can't use replace with a text column...You need to use UpdateText--it's not very pretty...
0
 
LVL 34

Expert Comment

by:arbert
ID: 11743776
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 11743869
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..
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 34

Expert Comment

by:arbert
ID: 11744003
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....
0
 
LVL 9

Expert Comment

by:miron
ID: 11744891
use substring function

-- cheers
0
 
LVL 1

Expert Comment

by:charlesejones
ID: 11745208
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?
0
 
LVL 34

Accepted Solution

by:
arbert earned 1000 total points
ID: 11745231
"For grins and giggles, try

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

Like was said above--simply using REPLACE will not work with TEXT/NTEXT columns....
0
 

Expert Comment

by:sitary
ID: 12270555
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
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question