Creating a sequence for a varchar2 field in Oracle

Question

I want to create a sequence for this varchar. It would have been easier had it been a number instead of varchar. In that case, I could do

seq_no := seq_no + 1;

But what can I do when I want to store next value in column as A0000002, when the previous value was A0000001 (to increment the number in the next varchar rowby 1)?

Answer 1

This can be done by

to_char(seq_no,'FM0000000')

your example can be done by creating 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 have used in dual ..but place this

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

in your required query ..this will create sequence as you mentioned

sqlfiddle

Answer 2

Sequences are purely numeric. However, you need a trigger anyway, so simply adapt such 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;