How can I remove unwanted carriage-returns and line-feeds from a SQL table?

A. Here is a script courtesy of fellow MVP, Bob Pfeiff. Note, it will only get rid of one CRLF per column, so you may want to stick the update in loop and keep doing it until you get a zero rowcount.

--example on pubs database in SQL 7

--insert a row with a carriage return and line feed in it

insert authors (au_id, au_lname, au_fname, phone, contract)
values ( '111-34-3420', char(10) + char(13), 'john', '222 333-8899', 1)

--find the row with the cr/lf in it

select
*
from
authors
where
au_lname = char(10) + char(13)

--update the column with the cr/lf with an empty string

update
authors
set
au_lname = stuff(au_lname, patindex(char(10)+ char(13), au_lname), 2, '')
where 
patindex(char(10) + char(13), au_lname) > 0

--look for the row with the cr/lf in it (should be gone)

select
*
from
authors
where
au_lname = char(10) + char(13)


Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish