Solved

Replace carriage return with ' '

Posted on 2004-08-07
8
24,756 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
 
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL:  Why do the single quotes make a difference for a date field? 5 35
VB6 ListBox Question 4 30
triggered use of sp_send_dbmail failure 2 22
Sql Query 4 0
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

919 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now