LTRIM/RTRIM not removing spaces

Question

using the script:

select 'XX' + LTRIM(RTRIM(Account_No))  
from mis.ahr.Arnel_Apple_BID_VPP_List2014Oct
where Account_No LIKE '%32216996%'

I still get

(No column name)
XX 32216996

what could be the cause? TIA

possible duplicate of [TSQL 2008 Using LTrim(RTrim and still have spaces in the data](http://stackoverflow.com/questions/3374888/tsql-2008-using-ltrimrtrim-and-still-have-spaces-in-the-data) — Jason C, Nov 12 '14 at 22:06
(Or a no-break space, or anything else like that; see linked question.) — Jason C, Nov 12 '14 at 22:08
I've had that problem. Try for nb-space. Tabs are trimmed i believe but not nbsps (ascii code 160). Add a REPLACE(Account_No, CHAR(160), '') and it will probably work. — Max, Nov 12 '14 at 22:25
okay thanks for all the suggestions! I really appreciate it! — dmbp5237, Nov 13 '14 at 01:06

Horaciux · Answer 1 · 2014-11-12T22:26:12.037

0

try this to see what character it is

select unicode(LEFT(Account_No,1)) 
from mis.ahr.Arnel_Apple_BID_VPP_List2014Oct
where Account_No LIKE '%32216996%'

Then use nchar( n) for replace

edited Nov 12 '14 at 22:26

answered Nov 12 '14 at 22:20

Horaciux

score 0 · Answer 2 · answered Nov 12 '14 at 23:00

You can try this, and let us know what 'Account_No' and 'hexBytes' columns show :

select Account_No,  
cast(Account_No as varbinary(500)) as hexBytes
from mis.ahr.Arnel_Apple_BID_VPP_List2014Oct
where Account_No LIKE '%32216996%'

This will show you what exactly is in Account_No.

2 Answers2