Warm tip: This article is reproduced from stackoverflow.com, please click
sql sql-server

Trim spaces in string

发布于 2020-03-28 23:15:13

I tried this code - 

UPDATE Table
SET Name = RTRIM(LTRIM(Name))

Data type of Name is varchar(25)

None of the leading and trailing spaces get removed. When I copy-paste one such Name, i get this - 

"big dash" "space symbol" ABC001

Why is this happening and how do trim the spaces ?

EDIT -

The question has already been answered. I found one more table with this problem. I get "- value" when i copy a column of a row. When I press the enter key at end of this copy-pasted value, i see more dashes. See image below -

Weird symbols

Questioner
InTheSkies
Viewed
14
分享
Maheswaran Ravisankar 2014-02-06 03:18

I suspect, some non readable(Non-ascii characters) inside the name column, that might not get removed as part of TRIM calls.

select convert(varbinary, Name) from table

Reading the HEX output from above query should reveal the same.

Kindly read this to find how to write functions to remove such characters.

Related issues

1
Why "LAST_VALUE(birthdate)" window function clause necessary compare to "FIRST_VALUE(birthdate)"?
2
Group by query with rollup
3
SQL Server. How to refresh the intellisense?
4
Error "Cannot insert explicit value for identity column in table" using C#
5
SQL query pivot, move values to top
6
Best way to implement an audit trail in SQL Server?
7
TLS 1.2 SQL Server and Delphi Client
8
Count amount of same value
9
SQL Insert does column order matter
10
SQL Server query problem. example is in excel sheet picture