Solved

Replace carriage return with ' '

Posted on 2004-08-07
8
24,759 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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 ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

733 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