Trim Leading Zeroes - 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 2005 Forums

Transact-SQL (2005)

Trim Leading Zeroes

Author

Topic

kellog1
Starting Member

35 Posts

Posted - 2010-06-29 : 15:13:59

Gurus,
I would like get rid of leading zeroes from Varchar column...

Here is what the data looks like currently..

Col1
006TH ST
005TH ST

Desired Result:

Col1
6th ST
5th ST

Thanks in advance.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-29 : 15:38:13

This is the easy way
select replace(Col1 ,'00','')

but this may be safer for your data

select replace(Col1,'00','')

from yourTable

where Col1 like '[0][0]%'

Jim

Everyday I learn something that somebody else already knew

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-29 : 17:05:37

What if there is 1 0...or 10?

Or 0's in the middle?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-29 : 17:17:29

[code]DECLARE @Sample TABLE
(
Data VARCHAR(20) NOT NULL
)

INSERT @Sample
VALUES ('006TH ST'),
('005TH ST')

SELECT Data,
SUBSTRING(Data, PATINDEX('%[^0]%', Data), LEN(Data)) AS Peso
FROM @Sample[/code]

N 56°04'39.26"
E 12°55'05.63"

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-30 : 10:29:34

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ','.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-30 : 10:31:09

Is that a new 2k8 syntax????



DECLARE	@Sample TABLE
	(
		Data VARCHAR(20) NOT NULL
	)

INSERT	@Sample(Data)
SELECT	'006TH ST' UNION ALL SELECT '005TH ST'

SELECT	Data,
	SUBSTRING(Data, PATINDEX('%[^0]%', Data), LEN(Data)) AS Peso
FROM	@Sample

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-30 : 10:48:24

Yes. Sorry for that. And thank you for fixing the sample data!

N 56°04'39.26"
E 12°55'05.63"

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-30 : 11:04:12

I'm liking the new syntax

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-01 : 02:27:24

quote:

Originally posted by Peso

DECLARE	@Sample TABLE
	(
		Data VARCHAR(20) NOT NULL
	)

INSERT	@Sample
VALUES	('006TH ST'),
	('005TH ST')

SELECT	Data,
	SUBSTRING(Data, PATINDEX('%[^0]%', Data), LEN(Data)) AS Peso
FROM	@Sample

N 56°04'39.26"
E 12°55'05.63"

Seems you are fully working on 2008 only

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources