Vladimir Yashin Vladimir Yashin - 1 year ago 81
SQL Question

How to fill some column with constant string followed by variable number

I need to fill a table column (in Oracle database) with string values that have variable part, e. g. AB0001, AB0002,...,AB0112...,AB9999, where AB is constant string part, 0001 -9999 is variable number part. i've tried the following solution in SQL for a table with 2 columns:

create table tbl1
(seq1 number(8),
string1 varchar(32));

tst number(8) :=0;
for cntr in 1..100
tst := cntr;
insert into TBL1 values (someseq.nextval, concat('AB',tst));
end loop;

But in this case I get STRING1 filled with values AB1,AB2,...,AB10,.. which is not exactly what I need.
How should I modify my script to insert values like AB0001,...,AB0010?

Answer Source

Either pad the number with zeros, or format it with leading zeros:

insert into TBL1
values (someseq.nextval, concat('AB', to_char(tst, 'FM0000'));

The 'FM' format modifier prevents a space being added (to allow for a minus sign).

For your specific example you don't need a PL/SQL block; you could use a hierarchical query to generate the data for the rows:

insert into tbl1(seq1, string1)
select someseq.nextval, concat('AB', to_char(level, 'FM0000'))
from dual
connect by level <= 100;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download