By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,779 Members | 1,078 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,779 IT Pros & Developers. It's quick & easy.

How to pad a sequence value with zeros

P: n/a
Hi,

I Oracle one can have :

select to_char(a_id_seq.nextval,'0000') from dual

Which fetches a sequence value and pads it with zeros
on the left. When the sequence value is more than
4 digits '####' is returned.

How do I do about the same thing in DB2 ?

select char(nextval_ for a_schema.a_id,'0000') doesn't work.

I've tried :

select replicate('0',4-len(A.id)),A.id from (
select nextval for a_schema.a_id_seq id
from sysibm.sysdummy1 ) A

But this returns a message saying q_id_seq can not
be specified in this context.

Any ideas or handy functions?

Thanks.

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
gi*******************@yahoo.com wrote:
Hi,

I Oracle one can have :

select to_char(a_id_seq.nextval,'0000') from dual

Which fetches a sequence value and pads it with zeros
on the left. When the sequence value is more than
4 digits '####' is returned.

How do I do about the same thing in DB2 ?

select char(nextval_ for a_schema.a_id,'0000') doesn't work.

I've tried :

select replicate('0',4-len(A.id)),A.id from (
select nextval for a_schema.a_id_seq id
from sysibm.sysdummy1 ) A

But this returns a message saying q_id_seq can not
be specified in this context.

Any ideas or handy functions?

Thanks.

A language independent trick (modulo various levels of syntactic sugar) is:
substr(char(mod(x, 10000)+10000),2,4)
A source of problem is negative x (and how mod treats that).
Nov 12 '05 #2

P: n/a

select char(nextval_ for a_schema.a_id,'0000') doesn't work.

Maybe 'select right(digits(nextval for a_schema.a_id),4) from
sysibm.sysdummy1' will work.
Nov 12 '05 #3

P: n/a
Gert van der Kooij wrote:
select char(nextval_ for a_schema.a_id,'0000') doesn't work.


Maybe 'select right(digits(nextval for a_schema.a_id),4) from
sysibm.sysdummy1' will work.

or
db2 => values substr(char(cast(next value for s as decimal(4, 0))), 1, 4)
db2 (cont.) => ;

1
----
0003

Note that for general to_char() support take a look at the function
library of the migration tool kit.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #4

P: n/a
> Which fetches a sequence value and pads it with zeros
on the left. When the sequence value is more than
4 digits '####' is returned.


------------------------- Commands Entered -------------------------
ALTER SEQUENCE a_id RESTART WITH 1;
--------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------- Commands Entered -------------------------
SELECT NEXT VALUE FOR a_id
, SUBSTR(DIGITS(NEXT VALUE FOR a_id)||'####'
,7+SIGN(SIGN(NEXT VALUE FOR a_id - 10000)+1)*4,4)
FROM SYSIBM.SYSDUMMY1;
--------------------------------------------------------------------

1 2
----------- ----
1 0001

1 record(s) selected.
------------------------- Commands Entered -------------------------
ALTER SEQUENCE a_id RESTART WITH 9999;
--------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------- Commands Entered -------------------------
SELECT NEXT VALUE FOR a_id
, SUBSTR(DIGITS(NEXT VALUE FOR a_id)||'####'
,7+SIGN(SIGN(NEXT VALUE FOR a_id - 10000)+1)*4,4)
FROM SYSIBM.SYSDUMMY1;
--------------------------------------------------------------------

1 2
----------- ----
9999 9999

1 record(s) selected.
------------------------- Commands Entered -------------------------
SELECT NEXT VALUE FOR a_id
, SUBSTR(DIGITS(NEXT VALUE FOR a_id)||'####'
,7+SIGN(SIGN(NEXT VALUE FOR a_id - 10000)+1)*4,4)
FROM SYSIBM.SYSDUMMY1;
--------------------------------------------------------------------
1 2
----------- ----
10000 ####

1 record(s) selected.
------------------------- Commands Entered -------------------------
SELECT NEXT VALUE FOR a_id
, SUBSTR(DIGITS(NEXT VALUE FOR a_id)||'####'
,7+SIGN(SIGN(NEXT VALUE FOR a_id - 10000)+1)*4,4)
FROM SYSIBM.SYSDUMMY1;
--------------------------------------------------------------------
1 2
----------- ----
10001 ####

1 record(s) selected.

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.