1

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
User987
  • 3,663
  • 15
  • 54
  • 115
  • 1
    What characters are the white space? Trim only removes spaces. Not tabs or other types of white space. – Martin Smith May 21 '17 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 May 21 '17 at 18:17
  • @MartinSmith How to remove the tabs/enter ? – User987 May 21 '17 at 18:18
  • 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 May 21 '17 at 18:22
  • @MichałTurczyn this goes with update statement ? – User987 May 21 '17 at 18:22
  • Well, I am not sure what you mean by that, but this should work: `UPDATEsomeTable set col = REPLACE(col, ... , ...)` – Michał Turczyn May 21 '17 at 18:23
  • [This](http://stackoverflow.com/a/35247507/92546) answer may help with removing whitespace characters in general. [This](http://stackoverflow.com/a/35245374/92546) answer shows the characters that `LTrim` and `RTrim` remove. – HABO May 22 '17 at 03:29

2 Answers2

1

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.
Murugan Sivananantha Perumal
  • 160
  • 5
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
Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38
  • 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. https://learn.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql – Mass Dot Net May 22 '17 at 18:19