CODEWITHSUNDEEP
oracle-database
totalitarian
totalitarian

Reputation: 3666

How to extract specific number from a string

My data might look like this

Goods Receipt required for document 5105250265 from Vendor 
Goods Receipt(s) have been fully taken for document 5105295587
Goods Receipt required for document 5105285525 from Vendor 
Please process Vendor invoice from CARDIFF COUNTY COUNC, reference 1801314625
Goods Receipt required for document 5105302265 from Vendor

The number I want to extract begins with a 5 and is always 10 digits long.

What would be the best way to extract this number?

The best could come up with is 

select regexp_replace(column, '[A-Za-z]')  from table

but this doesn't check the number starts with a 5 or is 10 digits long

Upvotes: 0

Views: 33

Answers (1)

Aleksej
Aleksej

Reputation: 22949

Assuming that you have at most one number matching your criteria in a row, you can try the following:

with test (text) as
(
select 'Goods Receipt required for document 5105250265 from Vendor' from dual union all 
select 'Goods Receipt(s) have been fully taken for document 5105295587' from dual union all
select 'Goods Receipt required for document 5105285525 from Vendor' from dual union all 
select 'Please process Vendor invoice from CARDIFF COUNTY COUNC, reference 1801314625' from dual union all
select 'Goods Receipt required for document 5105302265 from Vendor' from dual
) 
select regexp_substr(text, '5[0-9]{9}')
from test

Upvotes: 2

Related Questions