MS SQL Server Strip Away CR characters

rwheeler23
rwheeler23 used Ask the Experts™
on
I am moping up a SQL database and discover the updated version of one of our program crashes on certain records. When I look at one of the text fields in the table several records have the CR (13) character in the string. The vendor has confirmed that the new version of their code now longer allows that character.

What would the SQL syntax be an update statement on this one field in this table that would replace all CR characters with spaces?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
replace (field, char(13), '')
C# ASP.NET Developer
Top Expert 2010
Commented:
UPDATE mytable
SET myfield = replace(myfield, char(13), '')

or
UPDATE mytable
SET myfield = replace(myfield, CHAR(13) + CHAR(10), '')

if it has the line feed as well
Top Expert 2011

Commented:
possibly you want to consider replacing char(13) with char(10) instead of space(0/1)

have you and the vendor considered the impact of removing the  carriage return

will you also have a problem with line  feed char(10)?
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

rwheeler23President

Author

Commented:
This vendor is almost as large as Microsoft and simply blame this on my client because they were on an unsupported version because it was so old. The field is only 50 characters long and no one should have been putitng CR/LF's in there anyway. There new version does not support any non-ascii characters.
Top Expert 2011

Commented:
technically cr lf are ascii... they are just "not printable" characters ...

i admit it is bad practice in the first place to allow them into the field
have you also considered tab (09)

good luck
rwheeler23President

Author

Commented:
I just scanned the table and it was only the CR/LF combination that was in there and only in that one field. I am sure the vendor realized that and changed their code. My client had not upgraded in over 4 years. Another reason why keeping your head in the sand is not a good idea.
Top Expert 2011

Commented:
hmm sounds dangerously bleeding edge

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial