Hi,
I am extracting purchase order codes from a text column (VARCHAR2) in an Oracle database (11g R2) that contains invoice descriptions handwritten by accountants.
The purchase order code has this fixed structure of 10 characters:
- first two letters, USA city code
- third and fourth letters, always US
- fifth to tenth, six numbers.
Samples of data:
‘NYUS746082’
‘P.O. wcus216540’
‘CHUS412795-CHUS412796 Network maintenance 07-02-2012’
‘Security guards contract LAUS213789,DTUS556210 ,BSUS723005’
I am using this query which extract the first purchase order and warns me if the codes found are > 1:
SELECT REGEXP_SUBSTR(UPPER(INVOICE_DESCRIPTION), '(BS|CH|DT|LA|NY|WC)US\d{6}') AS PO_NUMBER,
REGEXP_COUNT(UPPER(INVOICE_DESCRIPTION), '(BS|CH|DT|LA|NY|WC)US\d{6}') AS PO_COUNT
FROM
(SELECT 'Limousine leasing 2014 (WCUS213789)' AS INVOICE_DESCRIPTION
FROM DUAL
);
Is it possible to extract all the matches in same column?
I have found some examples that split comma separated values but in my case the separator is not predictable.
Many thanks for your time,
Marco.