Substring of a string using Oracle (SUBSTRING or REGEXP_SUBSTR)

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)

Output:

1.S9C000019651
2.Null
3.S9C00019171

I suspect that your question is not specified in all the details it needs. For example, do you or do you not want to show the string, if it's something like **ABS9C000019651**? This does include S9C000019651 as a substring, but in most cases the business user would not want that selected. Also in your example there are nine digits following, you said seven. So, why are you showing all nine digits? (I am sure you have a reason, but it's not what you explained in English.) — , Aug 06 '21 at 14:42
What if S9C is followed by seven digits and then a letter? Like S9C0001234A2? This matches the pattern you stated (S9C followed by seven digits), but do you or do you not want this shown in the output? Does the "S9C followed by seven OR MORE digits" have to be a full **word**, meaning it must be preceded AND followed by space, punctuation etc. (non-WORD characters)? — , Aug 06 '21 at 14:45
@mathguy Thanks for your response. yes you are right, I need the string S9C and the next digits no matter how many digits after that. And If i get a letter in between S9C0001234A2 then i need the output till that letter. Hope i am clear now. — Robinhood, Aug 06 '21 at 15:13
How about my first question - if you have ABS9C0000112233 in the input, what do you need in the output? NULL? The entire string? Just S9C0000112233? Or something else? — , Aug 06 '21 at 15:17

Ankit Bajpai · Accepted Answer · 2021-08-06T17:54:12.277

1

You can try below REGEX -

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)
SELECT REGEXP_SUBSTR(STR, 'S9C\d{7,}')
  FROM DATA;

Demo.

edited Aug 06 '21 at 17:54

answered Aug 06 '21 at 15:19

Ankit Bajpai

13,128
4
25
40

This may be right or wrong - it depends on the OP's answer to my question (in comments to his question). I assume you saw it? Aside from that, what's the purpose of the `+` operator in your regular expression? – Aug 06 '21 at 15:21
@mathguy, I will update my answer accordingly if OP updates the question. BTW that `+` means pick all digits after `S9C` whether they are 7 or 8 or 9 or 10. – Ankit Bajpai Aug 06 '21 at 15:33
Wrong about the `+`. It means "take whatever precedes the plus and allow one or more occurrences of that in the regexp". Except in your regexp the plus is after another quantifier, the `{7,}`, and the plus coming after another quantifier is illegal. (Not sure how Oracle implemented that - if it throws an error or if it simply ignores the plus.) The "or more" part in "seven OR MORE digits" is the comma in your `{7,}` - that means "seven **or more** digits" without giving an upper bound. – Aug 06 '21 at 15:42
@mathguy, You are absolutely correct. I just tried it and updated my answer accordingly. – Ankit Bajpai Aug 06 '21 at 17:56
@AnkitBajpai Is it Possible to fetch the second matching string if its available in the column like this. `DO NOT USE CARRIER SPIDEX ZK 38 98 SH. S9C00011593 (SUPERSEDE BY S9C10204555 - WIN0020775)` In this case I want `S9C10204555` as an output. – Robinhood Aug 23 '21 at 11:37
@HiranyaSrikanth, For that you need to ask another question. I will reply there. – Ankit Bajpai Aug 23 '21 at 12:00
@AnkitBajpai – Robinhood Aug 23 '21 at 12:10
@AnkitBajpai https://stackoverflow.com/questions/68892510/substring-of-a-string-using-oracle-substring-or-regexp-substr-when-there-is-mu – Robinhood Aug 23 '21 at 12:16
@AnkitBajpai what if i need matching with only 8 digits after A9B else Null – Robinhood Aug 23 '21 at 13:24
@AnkitBajpai https://stackoverflow.com/questions/68893579/substring-using-oracle-when-there-is-multiple-matches – Robinhood Aug 23 '21 at 13:29
@AnkitBajpai https://stackoverflow.com/questions/68892510/substring-of-a-string-using-oracle-substring-or-regexp-substr-when-there-is-mu – Robinhood Aug 23 '21 at 15:09

Substring of a string using Oracle (SUBSTRING or REGEXP_SUBSTR)

1 Answers1