I need to extract data from a SQL server database to a file, problem is I need the file to be CSV with columns separated by either commas or colons.

One of the fields I need to extract allows users to type 'free text' so punctuation marks and carriage returns will be present in the extracted data, however I need to remove them. The punctuation marks are easily dealt with, but I have no idea how to remove the unwanted carriage returns.

Can anyone advise if there is an easy and efficient way to do this.

 

Do you know of a site called google?

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)