Data stored as strings can be problematic if you are exporting raw data from MySQL and using Excel or another application to analyze the data.
Characters like commas, new character lines, and double quotes can signal the start of a new column your target application. Removing these characters greatly reduces errors when opening the CSV file. For example, keeping character returns in your string data signals a new row in Excel which causes your data to become disjointed. Here is what you can use in your SELECT statement to remove problematic characters:
TRIM(REPLACE(REPLACE(REPLACE(REPLACE(column_name,'\r',' '),'\n',' '),",",""),'"',""))The code removes and replaces the following characters with either a space or nothing:
- Carriage returns
('\r') - New Lines
('\n') - Commas
- Double-quotes
Nesting the REPLACE function allows you to replace multiple characters for the same column. I wrap the REPLACE functions with the TRIM function just to be sure that I didn’t create any extra spaces at the beginning or end of the string.