rtrim/ltrim works good - but need some additional feedback

Using

select rtrim(ltrim(total)) as Total

from testtable

WHERE Total is NOT NULL

The results come back allright except for a small handful:

594
1242
17458
214
6971
29023
808
1
0
37
9
65
39
9
0
0
0
2
632
9
0
641
0
2438
148
2
1882
153
556
11839
616
1250
17709
187
6630
29548
803
7880
606
1600
1479
690
765
1630
3953
2418
9
75
1563
2
635
3
175
36
783
54
28
665
60
261
11995
679
1438
17548
118
6211
29543
797
7649
572
878
488
948
273
1756
2111
1205
2
54
1068
2
188
0
58

Showing me that there are still some spaces not getting trimmed off.. I would love to present this as trimmed as possible. Is there something to this that I am missing?

this this:

select rtrim(ltrim(cast(total as varchar(100)))) as Total

from testtable

WHERE Total is NOT NULL

|||

IF all of your values are in fact numbers, then use a cast instead of all the lrtim/rtrim.

Code Snippet

DECLARE @.MyTable table
( Col1 varchar(25)
)

INSERT INTO @.MyTable VALUES (' 29023')
INSERT INTO @.MyTable VALUES ('808')
INSERT INTO @.MyTable VALUES (' 1')

SELECT cast(Col1 as int) FROM @.MyTable

--
29023
808
1

|||

They are numbers within a string that looks to be compiled of binary specs of hell.

I am not seeing hex characters, but some binary. When I use your suggestion I get:

Msg 245, Level 16, State 1, Line 2

Conversion failed when converting the varchar value '

1' to data type int.

The field value is varchar(200) and there is no way I can change without some other functions blowing up.

In the end - I have some vbscript which I will be applying to a page so that this numeric data can be summed up.

|||

Perhaps this function will 'solve' your issue -it should eliminate all non-numerical characters.

Code Snippet

IF EXISTS
( SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'fnNumbersOnly'
)
DROP FUNCTION dbo.fnNumbersOnly
GO

CREATE FUNCTION dbo.fnNumbersOnly
( @.InParam varchar(50) )
RETURNS varchar(50)
AS

BEGIN
IF patindex( '%[^0-9]%', @.InParam ) > 0
BEGIN
WHILE patindex( '%[^0-9]%', @.InParam ) > 0
BEGIN
SET @.InParam = Stuff( @.InParam, patindex( '%[^0-9]%', @.InParam), 1, '' )
END
END
RETURN @.InParam
END
GO

Then use it like this:

Code Snippet

SELECT dbo.fnNumbersOnly( Total ) FROM TestTable

|||

OMG! Yes! Too cool!

Thank you!

|||

The problem was almost certainly due to something like embedded tab characters or other non-space whitespace characters.

If you run:

SELECT ' ' + char(9) + ' 123'

you will see that the second 5 spaces after the TAB are left behind. LTrim (and probably RTrim) only seem to remove spaces.