i have a tag in the following format: [AAA].[BBB].[CCC Level number]+some other stings here. for example
'[AAA].[BBB].[CCC Level 11].&[DDD3]'
'[AAA].[BBB].[CCC Level 1].&[EEE1]'
I want to get the number after Level
so the output of above two examples will be 11 and 1 respectively.
How do you do this in oracle 11g?
Thanks!
You could use the regexp_substr function:
SELECT
regexp_substr('[AAA].[BBB].[CCC Level 11].&[DDD3]', 'Level ([0-9]*)]', 1, 1, NULL, 1) AS val1,
regexp_substr('[AAA].[BBB].[CCC Level 1].&[EEE1]', 'Level ([0-9]*)]', 1, 1, NULL, 1) AS val2
FROM dual;
We match any digits that are between Level[space] and closing square bracket ]. The last parameter tells that you want to extract only the first subgroup, where each subgroup is defined by the parentheses - in this situation, we only have one subgroup, which is the number.
You need Oracle 11g for the subgroup extraction, but you've tagged the question with oracle11g tag so this should work for you.
Output:
VAL1 VAL2 ---- ---- 11 1
If it is possible that there will be levels with fractional parts, use this regular expression instead:
'Level ([0-9.]*)]'
