Search code examples
sqldatabaseoracle-databasesequences

Creating a sequence for a varchar2 field in Oracle

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)?

Solution

  • 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