Search code examples
sqlstringoracle-databaseregexp-substr

How to get all the patterns from the column in Oracle Sql

Below is a text in a column:

P348955: REMOVE NHA P210 PANEL PN : 6703ELM21 & REMOVE SUB ASSY PN: ELM2000-10-10 (ITEM 70) QTY 2EA & PN: ELM200-11-10 OR ALT (ITEM 75) QTY 1EA & PN ELM1057-1 QTY 1EA REQUESTED BY MMEECHAN REF ID 7369

I am looking to extract all similar patterns from that text, The pattern is defined as follows:

  1. Find anything that starts with 'PN' or 'PN: ' OR 'PN '
  2. Extract the Alphanumeric Characters that is followed by the above pattern, I am looking to extract the following from that text shown above

'PN : 6703ELM21' , 'PN: ELM2000-10-10' , 'PN: ELM200-11-10', 'PN ELM1057-1'

I tried the following:

SELECT REGEXP_SUBSTR( 'P348955: REMOVE NHA P210 PANEL PN : 6703ELM21 & 
REMOVE SUB ASSY PN: ELM2000-10-10 (ITEM 70) QTY 2EA & PN: ELM200-11-10 
OR ALT (ITEM 75) QTY 1EA & PN ELM1057-1 QTY 1EA REQUESTED BY MMEECHAN 
REF ID 7369', '(PN+/s*+:?+/s*+[A-Z][0-9])' FROM DUAL;

Desired output is PN : 6703ELM21, PN: ELM2000-10-10, PN: ELM200-11-10, PN ELM1057-1

Not sure how to get this output

enter image description here

Solution

  • This will get your the output as one row per occurence:

    WITH test_data (str, pattern) AS 
(
SELECT 'P348955: REMOVE NHA P210 PANEL PN : 6703ELM21 & 
REMOVE SUB ASSY PN: ELM2000-10-10 (ITEM 70) QTY 2EA & PN: ELM200-11-10 
OR ALT (ITEM 75) QTY 1EA & PN ELM1057-1 QTY 1EA REQUESTED BY MMEECHAN 
REF ID 7369'
,'PN\s*:?\s*(\w|-)*'
FROM DUAL
)
SELECT regexp_substr(str,pattern,1,level,'im' ) FROM test_data
CONNECT BY regexp_substr(str,pattern,1,level,'im' ) IS NOT NULL;

PN : 6703ELM21
PN: ELM2000-10-10
PN: ELM200-11-10
PN ELM1057-1