CODEWITHSUNDEEP
sqloracle-database
belmer01
belmer01

Reputation: 127

Oracle Insert Value Into Middle of Existing Value

This is similar to my previous question. I have the following partial docket numbers:

docket_number
-------------
2012JV592
2016DR138
2018JV84

If the docket number is less than 10 digits, then I need to insert 0's after the second letter until the lenth is 10 digits. Updated docket numbers would look like this.

docket_number
-------------
2012JV0592
2016DR0138
2018JV0084

Upvotes: 1

Views: 106

Answers (3)

Belayer
Belayer

Reputation: 14886

Is it the case that your docket_number should always follow the format 4 digits (year?) followed by 2 letters followed by 4 digits. Then simple sub-string of the docket_number and subsequent re-concatenation is sufficient.

select docket_number
     , substr(docket_number,1,6) || lpad(nvl(substr(docket_number,7),'0'),4,'0')  
  from test_dn 
 where length(docket_number) < 10
 order by docket_number;

and for update: 
update test_dn
   set docket_number = substr(docket_number,1,6) || lpad(nvl(substr(docket_number,7),'0'),4,'0')
 where length(docket_number) < 10;

If the format holds true then, depending on table size, this could be significantly faster as regular expressions are relative slow.

Upvotes: 0

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65278

You can split the data into three parts as digit group1, letters group, and digit group2 by using regexp_substr() functions, and lpad() function in order to add zeroes just before the second digit group, and then concatenate them directly by using || operators, assuming that you have the same data model for the whole table,

UPDATE t
   SET docket_number = regexp_substr(docket_number,'[[:digit:]]+')||
                       regexp_substr(docket_number,'[[:alpha:]]+')||
                       lpad('0',10-length(docket_number),'0')||
                       regexp_substr(docket_number,'[[:digit:]]+$')

Demo

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142743

One option might be to

  • split docket_number into two parts: first part contains digits and letters, the second contains the trailing number
  • result is concatenation of the first part and the second part left padded with zeroes up to total length of 10 characters
SQL> with test (docket_number) as
  2    (select '2012JV592' from dual union all
  3     select '2016DR138' from dual union all
  4     select '2018JV84'  from dual
  5    ),
  6  temp as
  7    (select docket_number,
  8         regexp_substr(docket_number, '[[:digit:]]+[[:alpha:]]+') part1,
  9         regexp_substr(docket_number, '[[:digit:]]+$') part2
 10     from test
 11    )
 12  select case when length(docket_number) < 10 then
 13              part1 || lpad(part2, 10 - length(part1), '0')
 14              else docket_number
 15         end result
 16  from temp;

RESULT
--------------------------------------------------------------------------------
2012JV0592
2016DR0138
2018JV0084

SQL>

How to update rows in a table? By using such a SELECT in UPDATE, e.g.

SQL> select * from test;

DOCKET_NUM
----------
2012JV592
2016DR138
2018JV84

SQL> update test a set
  2    a.docket_number =
  3      (with temp as
  4        (select b.docket_number,
  5              regexp_substr(b.docket_number, '[[:digit:]]+[[:alpha:]]+') part1,
  6              regexp_substr(b.docket_number, '[[:digit:]]+$') part2
  7         from test b
  8        )
  9       select case when length(t.docket_number) < 10 then
 10                        t.part1 || lpad(t.part2, 10 - length(t.part1), '0')
 11              else docket_number
 12              end
 13       from temp t
 14       where t.docket_number = a.docket_number
 15      );

3 rows updated.

SQL> select * from test;

DOCKET_NUM
----------
2012JV0592
2016DR0138
2018JV0084

SQL>

Upvotes: 1

Related Questions