SQL Remove Carriage return

Question

The data I am extracting contains carraige returns, I am not able to export the data and do any analysis on it.

i.e

Select system_outcome , contact_list_name , agent_classification , timestamp_dtm , phone_number, agent_name
From call_stats_view
Where date(timestamp_dtm) BETWEEN '2016-11-10' AND '2016-11-10'

This should give me a

Answered, Contactlist1, Sale, 2016-11-10, 07777777777, John King

when i extract the information it all ends up on different lines any idea how i can get around this?

I am using SQL Workbench (mysql) – Joyиal Ali Jan 12 '17 at 13:54 — Joyиal Ali, Jan 12 '17 at 13:54

score 7 · Answer 1 · answered Jan 12 '17 at 13:57

7

You should try something like the following:

SELECT REPLACE(REPLACE(yourColumn, CHAR(13), ''), CHAR(10), '')

to be certain that you remove all end of lines (CR & LF).

NOTE: you may considering replacing with ' ' (blank) if some useful information is separated by end of line only, otherwise you might get joined words.

answered Jan 12 '17 at 13:57

Alexei - check Codidact

22,016
16
145
164

What is CR and LF? – Joyиal Ali Jan 12 '17 at 13:59
1

White spaces that expand your string on multiple lines. Check [this](http://stackoverflow.com/questions/1279779/what-is-the-difference-between-r-and-n) for more details. – Alexei - check Codidact Jan 12 '17 at 14:00
1

@JoyиalAli Carriage Return and Line Feed – John Cappelletti Jan 12 '17 at 14:07
@Alexei do i have to do that for every column? – Joyиal Ali Jan 12 '17 at 14:22
@JoyиalAli - Yes. Another option is make the client perform the replacements. If you want the correction in Workbench, check [these options](http://www.sql-workbench.net/manual/options.html#options-multiline). – Alexei - check Codidact Jan 12 '17 at 14:34

SQL Remove Carriage return

1 Answers1