I'm trying to remove white spaces from a string in SQL but LTRIM
and RTRIM
functions don't seem to work?
Column:
[ProductAlternateKey] [nvarchar](25) COLLATE Latin1_General_CS_AS NULL
Query:
select REPLACE(ProductAlternateKey, ' ', '@'),
LEN(ProductAlternateKey),
LTRIM(RTRIM(ProductAlternateKey)) AS LRTrim,
LEN(LTRIM(RTRIM(ProductAlternateKey))) AS LRLen,
ASCII(RIGHT(ProductAlternateKey, 1)) AS ASCIIR,
ASCII(LEFT(ProductAlternateKey, 1)) AS ASCIIL,
ProductAlternateKey
from DimProducts
where ProductAlternateKey like '46783815%'
Result:
| COLUMN_0 | COLUMN_1 | LRTrim | LRLen | ASCIIR | ASCIIL | PRODUCTALTERNATEKEY |
---------------------------------------------------------------------------------
| 46783815 | 8 | 46783815| 8| 53 | 52 | 46783815 |
| 46783815 | 10|46783815 | 10| 10 | 52 | 46783815 |
Can it be other symbols if LTRIM
and RTRIM
are not working, like "Enter"?
This question is related to
sql
sql-server
tsql
sql-server-2008-r2
How about this?
CASE WHEN ProductAlternateKey is NOT NULL THEN
CONVERT(NVARCHAR(25), LTRIM(RTRIM(ProductAlternateKey)))
FROM DimProducts
where ProductAlternateKey like '46783815%'
Remove new line characters with SQL column data
Update a set a.CityName=Rtrim(Ltrim(REPLACE(REPLACE(a.CityName,CHAR(10),' '),CHAR(13),' ')))
,a.postalZone=Rtrim(Ltrim(REPLACE(REPLACE(a.postalZone,CHAR(10),' '),CHAR(13),' ')))
From tAddress a
inner Join tEmployees p on a.AddressId =p.addressId
Where p.MigratedID is not null and p.AddressId is not null AND
(REPLACE(REPLACE(a.postalZone,CHAR(10),'Y'),CHAR(13),'X') Like 'Y%' OR REPLACE(REPLACE(a.CityName,CHAR(10),'Y'),CHAR(13),'X') Like 'Y%')
In that case, it isn't space that is in prefix/suffix.
The 1st row looks OK. Do the following for the contents of 2nd row.
ASCII(RIGHT(ProductAlternateKey, 1))
and
ASCII(LEFT(ProductAlternateKey, 1))
Looks like the invisible character -
ALT+255
Try this
select REPLACE(ProductAlternateKey, ' ', '@')
--type ALT+255 instead of space for the second expression in REPLACE
from DimProducts
where ProductAlternateKey like '46783815%'
Raj
Edit: Based on ASCII() results, try ALT+10
- use numeric keypad
There may be 2 spaces after the text, please confirm. You can use LTRIM
and RTRIM
functions also right?
LTRIM(RTRIM(ProductAlternateKey))
Maybe the extra space isn't ordinary spaces (ASCII 32, soft space)? Maybe they are "hard space", ASCII 160?
ltrim(rtrim(replace(ProductAlternateKey, char(160), char(32))))
Source: Stackoverflow.com