Tags: a_id_seq, database, db2, dualwhich, fetches, mysql, nextval, oracle, pad, pads, select, sequence, sql, to_char, value, zeros, zeroson

How to pad a sequence value with zeros

On Database » DB2

4,117 words with 4 Comments; publish: Thu, 22 May 2008 13:14:00 GMT; (25093.75, « »)

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.

All Comments

Leave a comment...

  • 4 Comments
    • gimme_this_gimme_that.db2.todaysummary.com.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).

      #1; Thu, 22 May 2008 13:15:00 GMT
    • > 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.

      #2; Thu, 22 May 2008 13:16:00 GMT
    • Gert van der Kooij wrote:

      > 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

      #3; Thu, 22 May 2008 13:17:00 GMT
    • > 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.

      #4; Thu, 22 May 2008 13:19:00 GMT