Substring of a string using Oracle (SUBSTRING or REGEXP_SUBSTR) When there is multiple Matches

Question

Hi I am trying to fetch substring in oracle for the below two strings. I want result if the pattern matches starting with S9C and the having next numbers

For Eg: for the below two inputs I need output like

Input:

 1.CABLE : THERMINAL 3X2X0.25MM FPCP PLUS UNITRONIC S9C000019651  
 2.Motor Protection Relay EMR-3MPO-2S9CB1-1 (was IQ1000II / MP 3000)
 3.GREASE : BEM 41-132 3KG CARTRIDGE KLUBERPLEX S9C00019171 (Order by KG's required)
 4.DO NOT USE CARRIER SPIDEX ZK 38 98 SH. S9C00011593 (SUPERSEDE BY S9C10204555 - WIN0020775)

Output:

1.S9C000019651
2.Null
3.S9C00019171
4.S9C10204555

Or Else the Better way is to fetch first matching String from back of the text.

score 1 · Accepted Answer · answered Aug 23 '21 at 12:24

1

I think you can achieve the desired result with below script where you can pick the second group. If that is null, Pick the first group -

WITH DATA AS (SELECT 'CABLE : THERMINAL 3X2X0.25MM FPCP PLUS UNITRONIC S9C000019651' STR FROM DUAL
              UNION ALL
              SELECT 'Motor Protection Relay EMR-3MPO-2S9CB1-1 (was IQ1000II / MP 3000)' FROM DUAL
              UNION ALL
              SELECT 'GREASE : BEM 41-132 3KG CARTRIDGE KLUBERPLEX S9C00019171 (Order by KG''s required)' FROM DUAL
              UNION ALL
              SELECT 'DO NOT USE CARRIER SPIDEX ZK 38 98 SH. S9C00011593 (SUPERSEDE BY S9C10204555 - WIN0020775)' FROM DUAL)
SELECT NVL(REGEXP_SUBSTR(STR, 'S9C\d{7,}', 1, 2), REGEXP_SUBSTR(STR, 'S9C\d{7,}'))
  FROM DATA;

Demo.

answered Aug 23 '21 at 12:24

Ankit Bajpai

13,128
4
25
40

what if i need matching with only 8 digits after `A9B` else Null – Robinhood Aug 23 '21 at 13:18
Only 8 digits or at least 8 digits? – Ankit Bajpai Aug 23 '21 at 14:15
Only 8 digits after 8 – Robinhood Aug 23 '21 at 14:25
THen you can try REGEXP_SUBSTR(STR, 'S9C\d{8,8}') – Ankit Bajpai Aug 23 '21 at 14:28
No its not working. its elimination records having digits less than 8 – Robinhood Aug 23 '21 at 14:42
You can simplify the REGEXP_SUBSTR call with only one call to it and eliminate the NVL as REGEXP_SUBSTR already returns NULL if no match is found: `SELECT REGEXP_SUBSTR(STR, '.*(S9C\d{7,})', 1, 1, NULL, 1) FROM DATA;` – Gary_W Aug 25 '21 at 14:14

Substring of a string using Oracle (SUBSTRING or REGEXP_SUBSTR) When there is multiple Matches

1 Answers1

Linked