CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
HAxxor
HAxxor

Reputation: 1174

Sequences with PL SQL

I know how to create a sequence in pl sql. However, how would I set the values to all have say 3 digits? is there another sql statement to do this when I create a sequence?

so an example would be:

000
001
012
003

Thanks guys!

Upvotes: 1

Views: 3659

Answers (3)

ziggy
ziggy

Reputation: 15876

You can also use the lpad function.

In Oracle/PLSQL, the lpad function pads the left-side of a string with a specific set of characters.

For example:

lpad('tech', 7);    would return '   tech'
lpad('tech', 2);    would return 'te'
lpad('tech', 8, '0');   would return '0000tech'
lpad('tech on the net', 15, 'z');   would return 'tech on the net'
lpad('tech on the net', 16, 'z');   would return 'ztech on the net'

In your example you would use

 lpad('tech', 8, '0');  would return '0000tech'

i.e. if the string is less than 8 characters long, add 0s to the start of the string until the string is 8 characters long.

Ref: http://www.techonthenet.com/oracle/functions/lpad.php

Also, to add the 0s to the right you can use the rpad function.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231851

First, just to be clear, you do not create sequences in PL/SQL. You can only create sequences in SQL.

Second, if you want a column to store exactly three digits, you would need the data type to be VARCHAR2 (or some other string type) rather than the more common NUMBER since a NUMBER by definition does not store leading zeroes. You can, of course, do that, but it would be unusual.

That said, you can use the "fm009" format mask to generate a string with exactly 3 characters from a numeric sequence (the "fm" bit is required to ensure that you don't get additional spaces-- you could TRIM the result of the TO_CHAR call as well and dispense with the "fm" bit of the mask).

SQL> create table t( col1 varchar2(3) );

Table created.

SQL> create sequence t_seq;

Sequence created.

SQL> ed
Wrote file afiedt.buf

  1  insert into t
  2    select to_char( t_seq.nextval, 'fm009' )
  3      from dual
  4*  connect by level <= 10
SQL> /

10 rows created.

SQL> select * from t;

COL
---
004
005
006
007
008
009
010
011
012
013

10 rows selected.

Upvotes: 8

Jonny Cundall
Jonny Cundall

Reputation: 2622

haven't used plsql in a while, but here goes:

given an integer sequence myseq, 

to_char(myseq.nextval, '009')

Upvotes: 3

Related Questions