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.) — user5683823
– user5683823, Commented Aug 6, 2021 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)? — user5683823
– user5683823, Commented Aug 6, 2021 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
– Robinhood, Commented Aug 6, 2021 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? — user5683823
– user5683823, Commented Aug 6, 2021 at 15:17

Ankit Bajpai · Accepted Answer · 2021-08-06 17:54:12Z

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 6, 2021 at 17:54

answered Aug 6, 2021 at 15:19

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

user5683823 Over a year ago

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?

Ankit Bajpai Over a year ago

@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.

user5683823 Over a year ago

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.

Ankit Bajpai Over a year ago

@mathguy, You are absolutely correct. I just tried it and updated my answer accordingly.

Robinhood Over a year ago

@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.

|

Collectives™ on Stack Overflow

Substring of a string using Oracle (SUBSTRING or REGEXP_SUBSTR)

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related