Generating a leading zero - 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)

Generating a leading zero

Author

Topic

hrishy
Starting Member

47 Posts

Posted - 2008-05-13 : 14:44:05

Hi

I am unable to see how to generate a leading zero.



Table A 

declare @TableA table ( ID     numeric ,
			Fruits varchar(10)
			)
insert @TableA
          select 1,'Oranges'
union all select 2,'Mangoes'
union all select 3,'Apricots'


ID                     Table A 
1                      Apples
2                      Oranges
3                      Grapes
4                      Apricots


declare @TableB table ( seed    numeric ,			
			)
insert @TableB
          select 080513000448
union all select 080513000449
union all select 080513000450

Table B
seed
080513000448
080513000449
080513000450

I wrote the following query but i need generate a leading zero not sure which function can help maybe the right function but i am not sure how to use it in this case


SELECT	convert(varchar(10), getdate(), 12) + 
       (SELECT          
             	CASE 
	            WHEN SUBSTRING(ISNULL(max(seed),'00000'),1,6) = convert(varchar(10), getdate(), 12)
                     THEN SUBSTRING(ISNULL(max(seed),'00000'),7,12)
                      	ELSE '000000'
                    END AS SEED
         FROM B)
      + (row_number() 
	      over (order by Id))
            as SEED
FROM A



SEED
----
80513000451

The output which i need is

SEED
----
080513000451

rather then

SEED
----
80513000451

regards
Hrishy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-13 : 14:46:39

Concatenate with +.

'0' + ...

If you want the data stored with the leading zero, then you'll need to use a character data type instead such as varchar.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 14:47:27

RIGHT('000000000000' + CAST(Seed AS VARCHAR(12)), 12)

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

hrishy
Starting Member

47 Posts

Posted - 2008-05-13 : 14:52:36

Hi tkizer

Thanks for taking timeout and looking into this
but with your solution what i think
is when the year become 2010 or something it might not work.

Hi Peso

I did not understand your solution i am new to sqlserver where should i apply your casting

sorry if this a trivial question

regards
Hrishy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-13 : 14:58:40

quote:
Originally posted by hrishy

Hi tkizer

Thanks for taking timeout and looking into this
but with your solution what i think
is when the year become 2010 or something it might not work.

Then you should have mentioned this in your original post.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 14:59:23

May be this:-

SELECT	CAST(convert(varchar(10), getdate(), 12) + 
       (SELECT          
             	CASE 
	            WHEN SUBSTRING(ISNULL(max(seed),'00000'),1,6) = convert(varchar(10), getdate(), 12)
                     THEN SUBSTRING(ISNULL(max(seed),'00000'),7,12)
                      	ELSE '000000'
                    END AS SEED
         FROM B)
      + (row_number() 
	      over (order by Id)) AS varchar(20))
            as SEED
FROM A

hrishy
Starting Member

47 Posts

Posted - 2008-05-13 : 15:03:02

Hi Tkizer

I apologise for missing out a very crucial info .

Hi Vishakh

Thanks for taking timeout and responding i appreciate very much.
i notice that you use
AS varchar(20))

But i need the generated seed value of 12 digit string only wouldnt varchar(20) exceed that ?

regards
Hrishy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 15:04:40

quote:
Originally posted by hrishy

Hi Tkizer

I apologise for missing out a very crucial info .

Hi Vishakh

Thanks for taking timeout and responding i appreciate very much.
i notice that you use
AS varchar(20))

But i need the generated seed value of 12 digit string only wouldnt varchar(20) exceed that ?

regards
Hrishy

Nope. that wont cause a oproblem. If you are sure it will be 12 digits change length accordingly

hrishy
Starting Member

47 Posts

Posted - 2008-05-13 : 15:07:43

Hi Visakh16

Thanks again.
I am sure it would be 12 as per my current requirements.
do you mean to imply i change varchar(20) in your response to varchar(12).

regards
Hrishy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-13 : 15:09:26

If it will always be 12, then switch to char(12).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

hrishy
Starting Member

47 Posts

Posted - 2008-05-13 : 15:11:38

Hi Tkizer

Thanks again
Do you mean to say this


SELECT	CAST(convert(varchar(10), getdate(), 12) + 
       (SELECT          
             	CASE 
	            WHEN SUBSTRING(ISNULL(max(seed),'00000'),1,6) = convert(varchar(10), getdate(), 12)
                     THEN SUBSTRING(ISNULL(max(seed),'00000'),7,12)
                      	ELSE '000000'
                    END AS SEED
         FROM B)
      + (row_number() 
	      over (order by Id)) AS char(12))
            as SEED
FROM A

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-13 : 15:12:49

Yes. But instead of asking us, why not try it?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

hrishy
Starting Member

47 Posts

Posted - 2008-05-13 : 15:18:51

Hi Tkizer

I dont have acesses at this moment i would try that first thing when i have acesses to sqlserver2005.

Thanks again to all of you responded and helped visulaise the solution.

wish someday i would give back to this forum :-)

regards
Hrishy

hrishy
Starting Member

47 Posts

Posted - 2008-05-14 : 04:08:51

Hi

I tried this


SELECT	CAST(convert(varchar(10), getdate(), 12) + 
       (SELECT          
             	CASE 
	            WHEN SUBSTRING(ISNULL(max(seed),'00000'),1,6) = convert(varchar(10), getdate(), 12)
                     THEN SUBSTRING(ISNULL(max(seed),'00000'),7,12)
                      	ELSE '000000'
                    END AS SEED
         FROM B)
      + CAST (row_number() 
	      over (order by Id)) AS char(12))
            as SEED
FROM A

The output which i am getting is

0805140000001
0805140000002
0805140000003
....
08051400000010
08051400000011

whereas i need it to be
0805140000001
0805140000002
0805140000003
....
0805140000010
0805140000011

when the row_number becomes 10 or 11 the output should remain still in 12 digits only

how do i do that

regards
Hrishy

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 04:18:40

Did you try my suggestion posted 05/13/2008 : 14:47:27 ?

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

hrishy
Starting Member

47 Posts

Posted - 2008-05-14 : 04:21:32

Hi Peso

Thanks again for looking into this.

RIGHT('000000000000' + CAST(Seed AS VARCHAR(12)), 12)

Can you please let me know where in my whole sql should i be writing this ?

regards
Hrishy

hrishy
Starting Member

47 Posts

Posted - 2008-05-14 : 04:26:40

Hi Peso

I tried your suggestion and i am getting
80514000000000001

instead of
080514000001

regards
Hrishy

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 04:48:38

You are not very clear about your specifications about how to create sequence number!

Try this if sequence number is to be restarted from 1 for every new day

DECLARE @TableB TABLE (Seed NUMERIC)

INSERT	@TableB
SELECT	080512000999 UNION ALL
SELECT	080513000448 UNION ALL
SELECT	080513000449 UNION ALL
SELECT	080513000450 UNION ALL
SELECT	080514001011

SELECT	*
FROM	@TableB

SELECT	CONVERT(CHAR(6), GETDATE(), 12) + REPLACE(STR(1 + COALESCE(MAX(Seed), 0) % 100000, 6, 0), ' ', '0')
FROM	@TableB
WHERE	RIGHT(REPLICATE('0', 12) + CAST(Seed AS VARCHAR(12)), 12) LIKE CONVERT(CHAR(6), GETDATE(), 12) + '%'

Try this if sequence number is incremented regardless of day.

DECLARE @TableB TABLE (Seed NUMERIC)

INSERT	@TableB
SELECT	080512000999 UNION ALL
SELECT	080513000448 UNION ALL
SELECT	080513000449 UNION ALL
SELECT	080513000450

SELECT	*
FROM	@TableB

SELECT	CONVERT(CHAR(6), GETDATE(), 12) + REPLACE(STR(1 + COALESCE(MAX(Seed), 0) % 100000, 6, 0), ' ', '0')
FROM	@TableB

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

hrishy
Starting Member

47 Posts

Posted - 2008-05-14 : 05:54:24

Hi

Dissection

seed in table B as varchar

insert B
Values('080514000003')

SELECT * FROM B
080514000003

SELECT 	convert(varchar(10), getdate(), 12)  
This Gets 080514

SELECT 	convert(varchar(6), getdate(), 12) + 
	RIGHT('000000' + CAST(
      			 (SELECT		 
					CASE 
					WHEN SUBSTRING(max(seed),1,6) = convert(varchar(10), getdate(), 12)
					THEN SUBSTRING(max(seed),7,12)
					ELSE '000000'
					END AS SEED_URN
				FROM B) as varchar(6)),6)

This gets 
080514000003  --Which is 12 Digit and right

when i add the row_number portion the leading 0 is missed out when i add row_number

SELECT 	convert(varchar(6), getdate(), 12) + 
	RIGHT('000000' + CAST(
      			 (SELECT		 
					CASE 
					WHEN SUBSTRING(max(seed),1,6) = convert(varchar(10), getdate(), 12)
					THEN SUBSTRING(max(seed),7,12)
					ELSE '000000'
					END AS SEED_URN
				FROM B) as varchar(6)),6)
		 + row_number() 
				over (order by Id)
	as LearnerURN			--LearnerURN	
FROM A

This gets
80514000004 --which is missing a right 0

it should be
08051400004

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 06:04:43

This is were you are SO wrong!

SELECT * FROM B
080514000003

Seed is declared NUMERIC so the output will be
SELECT * FROM B
80514000003 -- No leading zero

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 06:09:45

New requirements. Seed is now not long NUMERIC, it is VARCHAR(12) or CHAR(12).

Try this if sequence number is to be restarted from 1 for every new day

DECLARE @TableB TABLE (Seed CHAR(12))

INSERT	@TableB
SELECT	'080512000999' UNION ALL
SELECT	'080513000448' UNION ALL
SELECT	'080513000449' UNION ALL
SELECT	'080513000450' UNION ALL
SELECT	'080514001011'

SELECT	*
FROM	@TableB

SELECT	CONVERT(CHAR(6), GETDATE(), 12) + REPLACE(STR(1 + RIGHT(COALESCE(MAX(Seed), '0'), 6), 6, 0), ' ', '0')
FROM	@TableB
WHERE	Seed LIKE CONVERT(CHAR(6), GETDATE(), 12) + '%'

Try this if sequence number is incremented regardless of day.

DECLARE @TableB TABLE (Seed CHAR(12))

INSERT	@TableB
SELECT	'080512000999' UNION ALL
SELECT	'080513000448' UNION ALL
SELECT	'080513000449' UNION ALL
SELECT	'080513000450'

SELECT	*
FROM	@TableB

SELECT	CONVERT(CHAR(6), GETDATE(), 12) + REPLACE(STR(1 + RIGHT(COALESCE(MAX(Seed), '0'), 6), 6, 0), ' ', '0')
FROM	@TableB

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources