Regex to get 00 if the string has less digits-CodePudding

I have a column that is giving me output like 'ABC2001' , 'ABC100145', 'ABC009282' ,' ABC1901'

I want to change this column value to have '00' in between literals and numbers if number is less than 6 digits. Something like -

COL_A
------------
ABC2001
ABC100145
ABC009282
ABC1901

Expected output

COL_B
------------
ABC002001
ABC100145
ABC009282
ABC001901

How to use regex for this ? Currently I am using

SELECT SUBSTR(COL_A,1,3)||LPAD(REGEXP_REPLACE(COL_A,'\D '),6,'0') FROM TAB

and it is giving me output like -

ABC210073
ABC210073

CodePudding user response：

You do not need (slow) regular expressions and can use simple string functions:

SELECT col_a,
       SUBSTR(col_a, 1, 3) || LPAD(SUBSTR(col_a, 4), 6, '0') AS col_b
FROM   table_name;

Which, for your sample data:

CREATE TABLE table_name (col_a) AS
SELECT 'ABC2001'   FROM DUAL UNION ALL
SELECT 'ABC100145' FROM DUAL UNION ALL
SELECT 'ABC009282' FROM DUAL UNION ALL
SELECT 'ABC1901'   FROM DUAL;

Outputs:

COL_A COL_B

ABC2001 ABC002001

ABC100145 ABC100145

ABC009282 ABC009282

ABC1901 ABC001901

COL_A	COL_B
ABC2001	ABC002001
ABC100145	ABC100145
ABC009282	ABC009282
ABC1901	ABC001901

db<>fiddle here

CodePudding user response：

Just for fun, for different prefixes:

usual string functions: trim/lpad/substr:

with table_name (col_a) AS (
SELECT 'ABC2001'   FROM DUAL UNION ALL
SELECT 'ABC100145' FROM DUAL UNION ALL
SELECT 'ABC009282' FROM DUAL UNION ALL
SELECT 'ABC1901'   FROM DUAL UNION ALL
-- other different prefixes:
select 'ABC2001'     from dual union all
select 'AB100145'    from dual union all
select 'A-BC9282'    from dual union all
select 'A8C2374'     from dual union all
select '7x-ABC32129' from dual union all
select '123ABC8942'  from dual
)
select v.*, prefix||num as col_b
from (
    select
      col_a,
      rtrim(col_a,'0123456789') as prefix,
      lpad(substr(col_a,1 length(rtrim(col_a,'0123456789'))),6,'0') as num
    from table_name
) v
;

DBFiddle

using regex functions:

with table_name (col_a) AS (
SELECT 'ABC2001'   FROM DUAL UNION ALL
SELECT 'ABC100145' FROM DUAL UNION ALL
SELECT 'ABC009282' FROM DUAL UNION ALL
SELECT 'ABC1901'   FROM DUAL UNION ALL
-- other different prefixes:
select 'ABC2001'     from dual union all
select 'AB100145'    from dual union all
select 'A-BC9282'    from dual union all
select 'A8C2374'     from dual union all
select '7x-ABC32129' from dual union all
select '123ABC8942'  from dual
)
select 
    col_a,
    regexp_replace(
       regexp_replace(col_a,'(\d )$','00000\1')
      ,'0*(\d{6})$'
      ,'\1'
    ) as col_b
from table_name
;

DBFiddle

regex solution for padding numbers to the maximum their length, ie not knowing max numbers length(if it's not hard-coded 6):

select 
    v.*,
    regexp_replace(
       regexp_replace(col_a,'(\d )$',rpad('0',max_num_length,'0')||'\1')
      ,'0*(\d{'||max_num_length||'})$'
      ,'\1'
    ) as col_b
from (
    select t.*, max(length(regexp_substr(col_a,'\d $')))over() as max_num_length
    from table_name t
) v
;

DBFiddle