Help with Trim - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with Trim

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-07-14 : 10:31:33

I have a field that I want to do like an "ALLTRIM" for just numeric values. I can do a RTRIM, but not sure how to do it so that it does not trim any of the numbers. Any ideas?

Example:


12 ECU
15 DDI
8  EOQ
7
3
12 DDI

After using the trim:

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-07-14 : 10:32:55

Maybe have a look into CHARINDEX(' ',<yourfield>,1).

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-14 : 10:48:37

select left(column,charindex(' ',column,1)-1) from yourTable

EDIT : If possible, do this in your Presentation layer

Madhivanan

Failing to plan is Planning to fail

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-07-14 : 12:01:38

Thanks for the example.

The first example, gives me zeros only.

The second example gives me this error:

'Invalid length parameter passed to the substring function.'

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-07-14 : 12:53:59

Based on your error description It apprears you are working with a text /ntext type datatype. Can you post some ddl and dml?

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-07-14 : 15:15:42

Yes I think so. The field is a NVARCHAR datatype field.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-07-14 : 15:28:57

quote:
Originally posted by ehorn

Based on your error description It apprears you are working with a text /ntext type datatype. Can you post some ddl and dml?

Or
Either the string wasnt found or the column has no data in it (NULL), so the -1 throws the error

eg:
SELECT LEFT('ABCDEF',CHARINDEX('Z','ABCDEF',1)-1) --String not found
Error - Invalid length parameter passed to the substring function.

SELECT LEFT('',CHARINDEX(' ','',1)-1) --Simulate empty field
Error - Invalid length parameter passed to the substring function.

SELECT LEFT('ABCDEF',CHARINDEX('D','ABCDEF',1)-1) --OK

Andy

Beauty is in the eyes of the beerholder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources