CODEWITHSUNDEEP
sqlsql-servert-sqlrecordtrim
User987
User987

Reputation: 3825

Removing trailing spaces and whitespaces from SQL Server column

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?

Upvotes: 1

Views: 13790

Answers (2)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

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

Upvotes: 1

Murugan Sivananantha Perumal
Murugan Sivananantha Perumal

Reputation: 178

Check the below;

  1. Find any special characters like char(10), char(13) etc in the field value.
  2. Check the status of ANSI_PADDING ON. Refer this MSDN article.

Upvotes: 1

Related Questions