How eliminate the tab space in the column in SQL Server 2008

Question

I have an column with email in table customer where the data in the column contains special character: TAB

When I do a select, I need to remove the TAB space from that column.

Means there is an empty TAB space followed by the EmailID: xyz.com

I tried using the LTRIM and RTRIM but that does not work here.

You could create a Sql function as described here https://stackoverflow.com/questions/14211346/how-to-remove-white-space-characters-from-a-string-in-sql-server — DMK, Sep 18 '17 at 14:26

score 165 · Accepted Answer · answered Oct 16 '12 at 03:12

165

Try this code

SELECT REPLACE([Column], char(9), '') From [dbo.Table]

char(9) is the TAB character

answered Oct 16 '12 at 03:12

KaR

@tofutim why should it? – roaima Dec 07 '16 at 11:28
@roaima because '\t' is the actual representation of a `tab` character? – Eric Wu Apr 24 '17 at 12:20
1

@EricWu in some contexts, certainly. In SQL generally? I doubt it, but I suppose it's possible. In SQL Server? Nope; see `select '\t'` – roaima Apr 24 '17 at 16:45

score 17 · Answer 2 · answered Nov 26 '15 at 01:27

17

UPDATE Table SET Column = REPLACE(Column, char(9), '')

answered Nov 26 '15 at 01:27

Andrei Karcheuski

score 10 · Answer 3 · answered Jan 11 '19 at 13:40

10

Use the Below Code for that

UPDATE Table1 SET Column1 = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(Column1, CHAR(9), ''), CHAR(10), ''), CHAR(13), '')))`

answered Jan 11 '19 at 13:40

Sumant Singh

score 0 · Answer 4 · answered Apr 27 '22 at 20:15

Starting with SQL Server 2017 (14.x) and later, you can specify which characters to remove from both ends using TRIM.

To TRIM just TAB characters:

SELECT TRIM(CHAR(9) FROM Email)
FROM MyTable

To TRIM both TAB and SPACE characters:

SELECT TRIM(CONCAT(CHAR(9), CHAR(32)) FROM Email)
FROM MyTable

Subroto Biswas · Answer 5 · 2019-01-30T04:11:05.167

-3

See it might be worked -------

UPDATE table_name SET column_name=replace(column_name, ' ', '') //Remove white space

UPDATE table_name SET column_name=replace(column_name, '\n', '') //Remove newline

UPDATE table_name SET column_name=replace(column_name, '\t', '') //Remove all tab

Thanks Subroto

edited Jan 30 '19 at 04:11

answered Jul 28 '16 at 12:45

Subroto Biswas

5 Answers5