Reputation: 3
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
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