CODEWITHSUNDEEP
sqloracle-database
Scott Reschke
Scott Reschke

Reputation: 3

PL SQL - Sequentially update only number in varchar

I have an existing table with data that is varchar. These codes were pre-determined by other-than-me so I cannot change them.

Every year I will need to run an insert statement which takes the previous year's data/codes and roll-them-over with the new year on them.

Example of existing data used for year 2019: CodeA19, CodeB19, CodeC19, CodeE19 etc

What I need to be able to do is sequentially add + 1 to the each code to change it to the new year (for 2020 in this case): CodeA20, CodeB20, CodeC20, CodeE20 etc

I've tried searching for regexpressions to do this but have had no luck on my own. I can't seem to find a way to separate out the mixed values to add to the numeric sequence.

Oracle db, PL sql, using SQL Dev

Thanks!

Upvotes: 0

Views: 108

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

You could use a regular expression to find values ending with the previous year, extract the remaining part, and tack on the new year; with hard-coded values something like:

select regexp_replace(code, '(.*)19$', '\1') as root,
       regexp_replace(code, '(.*)19$', '\1') || '20' as result
from your_table
where regexp_like(code, '(.*)19$');

or based on the actual current year, assuming you run it during the new year:

select regexp_replace(code, '(.*)' || to_char(add_months(sysdate, -12), 'RR') || '$', '\1') as root,
       regexp_replace(code, '(.*)' || to_char(add_months(sysdate, -12), 'RR') || '$', '\1')
         || to_char(sysdate, 'RR') as result
from your_table
where regexp_like(code, '(.*)' || to_char(add_months(sysdate, -12), 'RR') || '$');

both of which currently get:

ROOT       RESULT    
---------- ----------
CodeA      CodeA20   
CodeB      CodeB20   
CodeC      CodeC20   
CodeE      CodeE20

and then use that as an insert:

insert into your_table (code)
select regexp_replace(code, '(.*)' || to_char(add_months(sysdate, -12), 'RR') || '$', '\1')
         || to_char(sysdate, 'RR') as result
from your_table
where regexp_like(code, '(.*)' || to_char(add_months(sysdate, -12), 'RR') || '$');

db<>fiddle

The regexp pattern is a bit naive, but works for the examples; if you have more complicated values that might trip it up, or only want to match certain prefixes, then you would need to adapt that.

If the only numbers are the two-digit years at the end then you could simplify to use replace instead of regexp_replace, as Gordon suggested:

insert into your_table (code)
select replace(code, to_char(add_months(sysdate, -12), 'RR'), to_char(sysdate, 'RR')) as result
from your_table
where code like to_char(add_months(sysdate, -12), '"%"RR');

db<>fiddle

... but that may be a big 'if'.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Probably the simplest method is replace:

update t
    set code = replace(code, '19', '20')
    where code like '%19';

This assumes that codes do not have numbers.

Note: This seems like a very strange data model, if you are changing existing values liek this.

Upvotes: 1

Related Questions