Add leading zero for Legal Numbering

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

General SQL Server Forums

New to SQL Server Programming

Add leading zero for Legal Numbering

Author

Topic

Gratin
Starting Member

6 Posts

Posted - 2008-01-31 : 10:41:21

Good afternoon,

I hope the title is self explaining. Here is an example of what i would like to do:

Before...
1
1.1
1.1.1
1.2.1
1.3.1
1.10.1

After...
01
01.01
01.01.01
01.02.01
01.03.01
01.10.01

Thanks,
Phil.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-01-31 : 10:54:01

Could you elaborate , are the numbers currently in a table or are you passing these through a stored procedure?

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-31 : 10:58:05

[code]
select
a.num,
Legal_Num =
isnull(right('00'+parsename(a.num,3),2)+'.','')+
isnull(right('00'+parsename(a.num,2),2)+'.','')+
isnull(right('00'+parsename(a.num,1),2),'')
from
(
-- Test Data
select num = '1' union all
select num = '1.1' union all
select num = '1.1.1' union all
select num = '3.2.1' union all
select num = '1.3.1' union all
select num = '1.10.1'
) a

Results:
num Legal_Num
------ ---------
1 01
1.1 01.01
1.1.1 01.01.01
3.2.1 03.02.01
1.3.1 01.03.01
1.10.1 01.10.01

(6 row(s) affected)
[/code]

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2008-01-31 : 10:58:10

Are all of the digits a max of 2 bytes?

In any case, this is gonna take a sledge hammer approach to change that data

And just to add another insight....This is not numbering

That data type has to be char or varchar

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

Gratin
Starting Member

6 Posts

Posted - 2008-01-31 : 11:14:34

Thanks very much Michael ;)

It works perfectly.

See you,
Phil.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-31 : 11:17:43

Slighty simpler (just in case there are more parts than four).

select	a.num,
	substring(replace('.' + a.num, '.', '.0'), 2, 8000) as Legal_Num
from	(
		select num = '1'	union all
		select num = '1.1'	union all
		select num = '1.1.1'	union all
		select num = '3.2.1'	union all
		select num = '1.3.1'	union all
		select num = '1.10.1'
	) a

E 12°55'05.25"
N 56°04'39.16"

Gratin
Starting Member

6 Posts

Posted - 2008-02-01 : 06:26:59

Sorry Peso, we want only 2 digits between the dots. It doesn't give exactly the result required :
3.9.2 03.09.02
3.10 03.010
3.11 03.011
3.12 03.012
3.13 03.013
3.13.1 03.013.01

I'll stick with Michael solution and add 2 levels.

Thanks again to all of you.
Phil.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources