Trim leading zeros - 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)

Trim leading zeros

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-01-02 : 16:59:51

Guys,

Is there any way to trim leading zeros

000040 should be trimmed to 40, is there a function do it

any suggestions/inputs would help

Thanks

sshelper
Posting Yak Master

216 Posts

Posted - 2007-01-02 : 17:02:45

You can first CAST it to integer then CAST it back to VARCHAR:

SELECT CAST(CAST(YourColumn AS INT) AS VARCHAR(10))

Just a note. This will fail if your value cannot be converted to INT.

SQL Server Helper
http://www.sql-server-helper.com

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-01-02 : 17:04:45

Thats the thing I have some char values stored in the table, is there any work around to trim the leading zeros

sshelper
Posting Yak Master

216 Posts

Posted - 2007-01-02 : 17:06:04

If you have columns that cannot be converted to INT, then just change those that can be changed:

SELECT CAST(CAST(YourColumn AS INT) AS VARCHAR(10))
FROM YourTable
WHERE ISNUMERIC(YourColumn) = 1

SQL Server Helper
http://www.sql-server-helper.com

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-01-02 : 17:09:15

I also want trim the leading 0's of char values too

000040 to 40
and
00000P to P

Is there a way to do this

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-02 : 17:43:58

Using nr's suggestion...

declare @v table (data varchar(20))

insert	@v
select	'000010' union all
select	'000099' union all
select	'00001' union all
select	'0000x' union all
select	'00010p'

select	data,
	replace(ltrim(replace(data, '0', ' ')), ' ', '0')
from	@v

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-03 : 08:21:55

quote:
Originally posted by sshelper

If you have columns that cannot be converted to INT, then just change those that can be changed:

SELECT CAST(CAST(YourColumn AS INT) AS VARCHAR(10))
FROM YourTable
WHERE ISNUMERIC(YourColumn) = 1

SQL Server Helper
http://www.sql-server-helper.com

ISNUMERIC is not always reliable
http://aspfaq.com/show.asp?id=2390

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-03 : 08:38:52

Madhivanan,

The link is not working...

I got Service Unavailable !

May be you have directed too many lost yaks to that link..

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-03 : 09:14:32

May be

So many people are hitting that

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-01-04 : 08:54:53

I reported the SERVICE UNAVAILABLE issue to aspfaq.com as a bug in their migration to their new Look & Feel.

Looks like they have fixed it now ...

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-05 : 08:03:05

quote:
Originally posted by Kristen

I reported the SERVICE UNAVAILABLE issue to aspfaq.com as a bug in their migration to their new Look & Feel.

Looks like they have fixed it now ...

Kristen

Thanks Kristen

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources