Use SQL to remove Special ascll characters in text, such as enter, tab, and Space key

When exporting data from the database to excel, if the text contains the tab key, enter key, and Space key, but is placed in the datatable or displayed in the datagridview, the exception cannot be seen.

However, when exporting data to an Excel or CSV file, the format is inconsistent. How can I use SQL to clear these special ASCII characters?

Here is my solution:

The example passed the test in SQL Server 2008.

-- Replaces the text with the ASCII code containing the tab key, enter key, and Space key.

Select Replace (replace (mobile, char (9), ''), char (10),''), char (13 ),'')
From t_employee
Where employeecode = 'cx21262. 21594'

 

First, use the system function: Replace ('field _ name', 'From _ str', 'to _ str,

Note:

Cdb_name name of the table where the character or string is located
Field_name the field name of the character or string field
From_str string to be replaced
String replaced by to_str

 

-- Replace the carriage return
Update table_name
Set field_name = Replace (field_name, CHR (13 ),'')
Where instr (field_name, char (13)> 0

-- Replace line feed
Update table_name
Set field_name = Replace (field_name, CHR (10 ),'')
Where instr (field_name, char (10)> 0

-- Replace Blank

Update table_name
Set field_name = Replace (field_name, CHR (10 ),'')
Where instr (field_name, char (9)> 0table_name: Table Name
Field_name: column name