Can't remove Trailing spaces in the rows

Question

I have column with data like:

'2020193'
'3208391'
'1038291'
'9349203'

The data type is varchar and I can't change it to int (data managed in this datatype always).

I have some rows with trailing spaces like:

' 2222928'
' 3213331'

I need to remove that trailing space from start. I have tried SUBSTRING() or TRIM()/RTRIM()/LTRIM(), but didn't worked any of those.

select (rtrim(ltrim(doc_id))) from bpm.sales where len(doc_id) = 8
select left(doc_id,2) from bpm.sales where len(doc_id) = 8
select charindex(' ',doc_id) from bpm.sales where len(doc_id) = 8

Also, when I am trying to search the data like:

select doc_id from bpm.sales where doc_id = ' 2269203'

I am geting nothing where it exist in the column. With CHARINDEX() I got 0.

Can someone explain me this behaviour and suggest a solution?

Are you sure it's a space? Could it be a tab or some other invisible character? — ESG, Jun 25 '20 at 12:51
Run this query to identify the problem rows: SELECT doc_id FROM bpm.sales WHERE ISNUMERIC(doc_Id) = 0 — Preben Huybrechts, Jun 25 '20 at 12:52
I think the only thing we're sure of is that it's _not_ a space. It's some other non-printing character. Have a look at this answer: https://stackoverflow.com/a/3917698/5790584 — Eric Brandt, Jun 25 '20 at 13:00
What is the result of those SELECT statements? They don't change values (you need UPDATE for that), just report on existing ones. — Hans Keﬆing, Jun 25 '20 at 13:06

score 0 · Answer 1 · answered Jun 25 '20 at 13:00

0

You can get rid of everything up to the first character you do want:

select stuff(doc_id, 1, patindex('%[^0-9a-zA-Z]%', doc_id) - 1, '')

answered Jun 25 '20 at 13:00

Gordon Linoff

1,242,037
58
646
786

Can't remove Trailing spaces in the rows

1 Answers1