CODEWITHSUNDEEP
sqlregexoracle11gregexp-substr
Anonimus
Anonimus

Reputation: 3

REGEXP_SUBSTR get second match

I have this String : 

"Y::_~XXXXXX XXXXXX~MIGRATION~_~_~_~_~0~JOD50.000~USD257.43::JOD"

I wanted to get the list of currencies via Regex.

So far, I can only get the first match using this query:

SELECT 
REGEXP_SUBSTR('Y::_~XXXXXX XXXXXX~MIGRATION~_~_~_~_~0~JOD50.000~USD257.43::JOD','[a-zA-z]{3}$') 
from dual;

Can you please help me get the second match which is USD?? Thanks. :D

By the way, I am planning to use the Regex dynamically.

So given any output of String, I wanted to get 3-length String : either by occurence

"Y::_~XXXXXX XXXXXX~MIGRATION~_~_~_~_~0~JOD50.000~USD257.43::JOD"

JOD

USD

Upvotes: 0

Views: 3618

Answers (1)

Frank Schmitt
Frank Schmitt

Reputation: 30845

To get the second occurrence of your pattern, you should set the occurrence parameter of REGEXP_SUBSTR to 2:

SELECT REGEXP_SUBSTR('Dummy text 123USD another dummy text 456USD', 
  '[0-9]{3}USD',
  1,
  2) from dual

UPDATE

To get all groups of 3 letters that are surrounded by non-letters, you can use this query:

select match, substr(match, 2, 3) from (
SELECT 
  REGEXP_SUBSTR('Y::_~XXXXXX XXXXXX~MIGRATION~_~_~_~_~0~JOD50.000~USD257.43::JOD',
  '(^|[^a-zA-Z])[a-zA-z]{3}([^a-zA-Z]|$)',
  1,
  level) as match
from dual
connect by level <= 100
) where match is not null

This - matches all groups of 3 letters [a-zA-Z]{3} - that either occur at the start of the string ^ or have a non-letter character in front [^a-zA-Z] - and either occur at the end of the string$or have a non-letter character afterwards[^a-zA-Z] - uses CONNECT BY to generate the first 100 numbers using the LEVEL pseudocolumn - feeds the level as occurrence parameter into REGEXP_SUBSTR - excludes all invalid matches with WHERE MATCH IS NOT NULL - extracts the relevant part of the match with SUBSTR

(you could also use negative lookahead / lookbehind to ensure you have groups of exactly 3 letters, of course)

Upvotes: 1

Related Questions