CODEWITHSUNDEEP
sqlstringoracle-databaseplsql
Vladimir Yashin
Vladimir Yashin

Reputation: 3

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



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

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?

Upvotes: 0

Views: 1752

Answers (3)

Zigri2612
Zigri2612

Reputation: 2310

try this one

INSER INTO table_name(code)
VALUES(CONCAT('AB', LPAD('99', 4, '0'));

or You can Update on the basis of PK after insertion

UPDATE table_name SET code = CONCAT('AB', LPAD(PK_Column_Name, 4, '0') ;

or You Can Use Triggers

CREATE TRIGGER trgI_Terms_UpdateTermOrder
ON DB.table_name 
AFTER INSERT
AS
BEGIN
    UPDATE  t
    SET     code = CONCAT('AB', LPAD(Id, 4, '0') 
    FROM    DB.table_name t INNER JOIN inserted i ON t.Id = I.Id
END;
GO

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191550

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;

Upvotes: 2

Frank Ockenfuss
Frank Ockenfuss

Reputation: 2043

use the lpad function

select lpad(1, 4, '0') from dual
--> '0001'

Upvotes: 1

Related Questions