2

I can't achieve to make sqlfiddle work so here is my simple code : 

select REGEXP_SUBSTR(' EG SUZ SG SIN blabla ', '^(\s?[A-Z]{2} [A-Z]{3}\s?){2}') from dual;

I want to get SG SIN. I know REGEXP_SUBSTR got a parameter used to get the nth occurrence but when I write 

select REGEXP_SUBSTR(' EG SUZ SG SIN blabla ', '^(\s?[A-Z]{2} [A-Z]{3}\s?){2}'),1,2) from dual;

it returns nothing and 1,1 returns EG SUZ SG SIN so my guess is that the occurrence is not rightly splitted,but I don't know why, can you explain me?

So4ne
  • 1,124
  • 17
  • 38
  • I can't try it right now so I'm just adding a comment. You can try removing the {2} from the regex, then try the REGEXP_SUBSTR(.... 1, 2). I believe that should work. – Tamas Rev May 04 '16 at 10:02
  • @tamasrev that doesn't work. I also tried to put out the first \s? from the matching group with and without the {2} – So4ne May 04 '16 at 10:07
  • in your first regex it should be `select REGEXP_SUBSTR(' EG SUZ SG SIN blabla ', '^(\s?[A-Z]{2} [A-Z]{3}\s?){2}') from dual;` – rock321987 May 04 '16 at 10:10
  • @rock321987 oh yes my bad – So4ne May 04 '16 at 10:19
  • More specs are needed before a proper answer can be given. Are you looking for specific text of `'SG SIN'`? Or are you looking for the 3rd and 4th words of the string, or the 2nd set of 2 uppercase characters followed by 3 uppercase characters? Please ad some more specifics. – Gary_W May 04 '16 at 18:57
  • @Gary_W an answer has already be given, do you mean I have to clarify my question for other viewers? – So4ne May 09 '16 at 15:40
  • I'm just pointing out that the accepted answer is very specific as to the position of `'SG SIN'` and may not match if `'SG SIN'` is the 3rd set, is lowercase, at the start of the line, etc. Your only spec was to "get SG SIN" and the position was discerned from your regex. If it meets your needs, great but be aware if the position is different the accepted answer will not find it. I highly suggest you at least comment that for the next developer that will maintain that code so your intention is understood. – Gary_W May 10 '16 at 14:18

1 Answers1

1

This will work

select REGEXP_SUBSTR(' EG SUZ SG SIN blabla ', '([A-Z]{2} [A-Z]{3}(\s|$))', 1, 2) from dual;
rock321987
  • 10,942
  • 1
  • 30
  • 43
  • Yes thank you... Can you explain why the '^' blocks? as it's not in the ( ) group – So4ne May 04 '16 at 10:26
  • `^` is anchor which marks starting of the string..You can get all the string with the given pattern using the count available in oracle – rock321987 May 04 '16 at 10:33
  • To clarify, this answer will give you the 2nd occurrence of a set of two uppercase letters followed by a space followed by three uppercase letters followed by a whitespace (non-printing) character (space, tab, and form feed, etc) or the end of the line. If the pattern is not found, NULL will be returned. Make sure this describes exactly the part of the string and text you are after as it is very specific. – Gary_W May 04 '16 at 19:05