Regex to find a letter followed by 5 digits

September 12, 2023

I am using Oracle SQL and need a way to locate and output a letter followed by 5 digits. This may not exist at all or may be surrounded by other characters. Here is some sample data:

select '**' col from dual
union all
select '+D91882' from dual
union all
select '3DD12523' from dual
union all
select 'Testing' from dual
union all
select 'A12345' from dual
union all
select '3DR4096' from dual
union all
select 'G92648RE' from dual
union all
select 'D123456' from dual

The output from this in order listed should be:
null
D91882
D12523
null
A12345
G92648
D12345

>Solution :

That’s regexp_substr with appropriate regular expression: letter followed by 5 digits.

Sample data:

SQL> with test as
  2  (select '**' col from dual
  3  union all
  4  select '+D91882' from dual
  5  union all
  6  select '3DD12523' from dual
  7  union all
  8  select 'Testing' from dual
  9  union all
 10  select 'A12345' from dual
 11  union all
 12  select '3DR4096' from dual
 13  union all
 14  select 'G92648RE' from dual
 15  union all
 16  select 'D123456' from dual)

Query:

 17  select col,
 18    regexp_substr(col, '[[:alpha:]][[:digit:]]{5}') result
 19  from test;

COL      RESULT
-------- --------
**
+D91882  D91882
3DD12523 D12523
Testing
A12345   A12345
3DR4096
G92648RE G92648
D123456  D12345

8 rows selected.

SQL>