Removing trailing spaces and whitespaces from SQL Server column

Question

I have a column in my SQL Server database and it has white spaces from left and right site of the record. Basically it's a nvarchar(250) column.

I have tried removing white spaces completely like this:

UPDATE MyTable 
SET whitespacecolumn = LTRIM(RTRIM(whitespacecolumn))

But this didn't work out at all, the whitespace is still there. What am I doing wrong here?

What characters are the white space? Trim only removes spaces. Not tabs or other types of white space. — Martin Smith
– Martin Smith, Commented May 21, 2017 at 18:15
Im not sure which kind of charachters they are, but theres an empty space in front of the records like this: (this space here) username — User987
– User987, Commented May 21, 2017 at 18:17
You need to know by what number they are represented in particular encoding. For example in UTF-8, 13 number is for space, then you can replace it with REPLACE(col, char(13), ''). Since I am not sure about encoding (most surely it's UTF-8), I would suggest trying yourself. — Michał Turczyn
– Michał Turczyn, Commented May 21, 2017 at 18:22

Murugan Sivananantha Perumal · Accepted Answer · 2017-05-21 18:24:22Z

1

Check the below;

answered May 21, 2017 at 18:24

1881 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kannan Kandasamy · Accepted Answer · 2017-05-21 18:26:58Z

1

I think replace is the way as you are looking to update

UPDATE MyTable SET whitespacecolumn = Replace(whitespacecolumn, ' ', '')

you can try doing select first and then prefer to update

SELECT *, Replace(whitespacecolumn, ' ', '') from MyTable

LTRIM, RTRIM will remove spaces in front and rear of column. In 2016 you can use TRIM function as below to trim special characters as well:

SELECT TRIM( '.,! ' FROM  '#     test    .') AS Result;

Output:

# test

answered May 21, 2017 at 18:26

Kannan Kandasamy

14.1k3 gold badges28 silver badges40 bronze badges

Support for the TRIM() function is available in SQL Server 2017; as far as I can tell, it is not available in SQL Server 2016 or below. learn.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql