How to convert a number to a three-character string using an Oracle sequence

Question

I need to create an Oracle 12c sequence and convert it from a number to a three-character string that has the format of 001, 002, 003 etc.

Here is an example of a sequence:

CREATE SEQUENCE supplier_seq
  MINVALUE 1
  MAXVALUE 999
  START WITH 1
  INCREMENT BY 1
  CACHE 1;

Taking the above sequence, I now need to convert it to a three character string as shown above.

Answer 1

It is not possible using the only sequence.

But yes you can use the following function, wherever sequence is used, to convert the values of sequence to the required format.

SELECT LPAD(supplier_seq.nextval,3,0) FROM DUAL; -- 001, 002, 003

Hope, This is what you are looking for.