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.

rtrim in sql server 2000

Hi,
What is wrong with the following query?
select a.name, rtrim(a.name) from (
select top 100 name from dbo.table1 )a
whre table1 has the following in name column
A.B.xyz<space>
AB.xyz<space>
and so on...
When i cut and paste the result set from query analyzer into Excel, i
still see the trailing space.
BTW, i am using sql server 2000
Thanks in advance,
TamasWhat data type is the name column? The char/nchar/binary datatypes are of
fixed length, while varchar/nvarchar/varbinary are variable.
"Trimming spaces from fixed length columns is futile. You will be overpadded
."
ML
http://milambda.blogspot.com/|||what is the a.name datatype ?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<tamashee@.yahoo.com> wrote in message
news:1145399053.665196.72630@.e56g2000cwe.googlegroups.com...
> Hi,
> What is wrong with the following query?
> select a.name, rtrim(a.name) from (
> select top 100 name from dbo.table1 )a
> whre table1 has the following in name column
> A.B.xyz<space>
> AB.xyz<space>
> and so on...
> When i cut and paste the result set from query analyzer into Excel, i
> still see the trailing space.
> BTW, i am using sql server 2000
> Thanks in advance,
> Tamas
>

rtrim function

hi,

for example; select field1 from table

field1 value=codesample

I want to change field1 value with rtrim function than field1 value must be codesam. How can I do:?

Hi,

sorry but I didn't understand your request, what do you want to achieve, could you please describe this again ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

rtrim and ltrim?

I have some data that contains spaces both before and after the text string, and now I'm wondering what would be the best method to remove these blanks (sometimes there are no blanks, so I can't check with a specifik width)?

Is it possible to do something like:

set foo = ltrim(rtrim(foo))

or do I have to split it into 2 steps?

This trimming will be done in update & insert statements

// Patiltrim(rtrim(...)) is fine, you can use this in one step in either select, insert and/or update.|||Thanks for the quick reply! :)

RTrim and LTrim with UPDATE

You all have been so much help, but I've discovered yet another problem. I'm trying to clean up my table using the following command:
UPDATE dbo.TableName
SET First_Name = LTrim(RTrim(First_Name))But it does not seem to have any effect. Thoughts? Thanks!Should work fine. It's possible that your string has characters that appear as spaces, but which are actually a different ASCII value.|||Thr LTrim function works fine when I use it in conjunction with SELECT, just not UPDATE.|||Thr LTrim function works fine when I use it in conjunction with SELECT, just not UPDATE.

Is it possible you are dealing with a char (and not a varchar) column?

Regards,

hmscott|||Yes, that's correct. Is that wrong?|||Char is fixed length, so your data will be padded.
Better to use varchar in this situation.
In fact, char should only be used when the length is fixed and is under about 10 characters.
These days, zip codes are one of the few elements I store as char.|||Thanks, I originally switched from NVARCHAR, so I will update the tables..

Rtrim

If Rtrim doesn't catch space at the end is there a way to catch strings that match but don't seem to get selected correctly when matching?Um, what do you mean by "Rtrim doesn't catch space at the end"? Or by "catch strings that match but don't seem to get selected correctly when matching"?|||Some sample data and some code would be helpfull.|||Is it possible there is some unwriteable chars there? Ie ASCII below 32?

I don't if the show up or not in SQL-Server, just related to C++ programming where unwriteable chars can mess up things.|||use this to check if the last char really is a space...

SELECT
ASCII(SUBSTRING(yourcolumnname, len(yourcolumnname), 1))
FROM yourtable