I have a column of partial docket numbers.
docket_number
-------------
08DR2197
17JV1398
98JV2954
84JS249
76JV16391
66JV1616
If the docket number begins with a 6, 7, 8, or 9 then insert a "19" to the beginning of the docket number. If the docket number begins with anything else, then insert a "20" to the beginning of the docket number. Results would look like this.
docket_number
-------------
2008DR2197
2017JV1398
1998JV2954
1984JS249
1976JV16391
1966JV1616
select case when substr(docket_number, 1, 1) in ('6', '7', '8', '9') then '19'
else '20'
end || docket_number as docket_number
from your_table
I would just use string comparisons:
update t
set docket_number = (case when docket_number >= '60' and docket_number <= '99'
then '19' else '20'
end) || docket_number;
Try this option:
UPDATE yourTable
SET docket_number = CASE WHEN REGEXP_LIKE (docket_number, '^[6-9]')
THEN '19' ELSE '20' END || docket_number;
I am using a regex approach to check the first digit. The option given by @Littlefoot might actually outperform this, but the above code is a bit more concise.
