Geek Answers Handbook

Creating a sequence for varchar2 field in Oracle

I want to create a sequence for this varchar. It would be easier if there were a number instead of a varcarch. In this case, I could do

seq_no: = seq_no + 1;

But what should I do when I want to save the next value in a column as A0000002, when the previous value was A0000001 (to increase the number in the next row of varchar row 1)?

+4
source share
4 answers

This can be done using

to_char(seq_no,'FM0000000')

your example can be done by creating a sequence in oracle

 create sequence seq_no start with 1 increment by 1;

then

 select 'A'||to_char(seq_no.nextval,'FM0000000') from dual;

Right now I used in double ... but posted this

 'A'||to_char(seq_no.nextval,'FM0000000')

in the requested request .. this will create a sequence as you mentioned

sqlfiddle

+7
source

Sequences are purely numerical. However, in any case, you need a trigger, so just adapt this trigger to insert the desired prefix:

 CREATE OR REPLACE TRIGGER FOO_TRG1 BEFORE INSERT ON FOO REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN IF :NEW.FOO_ID IS NULL THEN SELECT 'A' || TO_CHAR(FOO_SEQ1.NEXTVAL, 'FM0000000') INTO :NEW.FOO_ID FROM DUAL; END IF; END FOO_TRG1; / ALTER TRIGGER FOO_TRG1 ENABLE;
+5
source

If you can really use a virtual column as defined in the CREATE TABLE syntax. This makes it more easily extensible if necessary.

Here is a working example.

 SQL> create table tmp_test ( 2 id number(7,0) primary key 3 , col1 number 4 , seq varchar2(8 char) generated always as ( 5 'A' || to_char(id, 'FM0999999')) 6 ); Table created. SQL> SQL> create sequence tmp_test_seq; Sequence created. SQL> SQL> create or replace trigger tmp_test_trigger 2 before insert on tmp_test 3 for each row 4 begin 5 6 :new.id := tmp_test_seq.nextval; 7 end; 8 / Trigger created. SQL> show errors No errors. SQL> SQL> insert into tmp_test (col1) 2 values(1); 1 row created. SQL> SQL> select * from tmp_test; ID COL1 SEQ ---------- ---------- -------------------------------- 1 1 A0000001

Having said that; you would be better off if you hadn’t done this, if you had no incredibly urgent business need. There is little point in making life more difficult for you by adding constant value to a number. Since A will always be A, it does not matter if he is there or not.

+2
source

If the format is always a letter followed by 7 digits, you can do:

 sequence = lpad(substr(sequence,2,7)+1,7,'0')
0
source

More articles:


All Articles