0

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

StackOne
  • 260
  • 2
  • 16

2 Answers2

0

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
Koen Lostrie
  • 14,938
  • 2
  • 13
  • 19
0

Another alternative:

SELECT substr
FROM (
    SELECT REGEXP_SUBSTR(str, 'PN(\s|\:)+(\d|\w)+?[^ ]+', 1, LEVEL) AS substr
    FROM (
        SELECT 'P348955: REMOVE NHA P210 PANEL PN : 6703ELM21 and REMOVE SUB ASSY PN: ELM2000-10-10 (ITEM 70) QTY 2EA and PN: ELM200-11-10 OR ALT (ITEM 75) QTY 1EA snd PN ELM1057-1 QTY 1EA REQUESTED BY MMEECHAN REF ID 7369' AS str
        FROM DUAL
    )
    CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(str, 'PN(\s|\:)+(\d|\w)+?[^ ]+')) + 1
)
WHERE substr is not null;
KOBER
  • 59
  • 1
  • 6