Solved

Replace carriage return with ' '

Posted on 2004-08-07
8
24,757 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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 250 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

773 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