Oracle正则表达式从最后一次出现时拆分字符串 [英] Oracle regular expression split string from last occurence
本文介绍了Oracle正则表达式从最后一次出现时拆分字符串的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我仍然在oracle中学习正则表达式,我陷入了中间,下面是我的示例代码:
I am still learning regexp in oracle I am stuck in middle, below is my sample code:
with t(val)
as
(
--format: xyz_year_month_date
select 'my_new_table_2015_06_31' from dual
union all
select 'my_new_table_temp_2016_06_31' from dual
)
select reverse(regexp_substr(reverse(val),'[^_]+',1,4)) col4,
reverse(regexp_substr(reverse(val),'[^_]+',1,3)) col3,
reverse(regexp_substr(reverse(val),'[^_]+',1,2)) col2,
reverse(regexp_substr(reverse(val),'[^_]+',1,1)) col1
from t;
Output:
COL4 COL3 COL2 COL1
table 2015 06 31
temp 2016 06 31
Expected output:
COL4 COL3 COL2 COL1
my_new_table 2015 06 31
my_new_table_temp 2016 06 31
谢谢.
推荐答案
您可以通过提取不同的捕获组(用圆括号()
括起来)来实现无双重反转的操作:
You can do it without the double reverse by extracting different capture groups (surrounded in round ()
brackets):
WITH t ( VAL ) AS (
SELECT 'my_new_table_2015_06_31' FROM DUAL UNION ALL
SELECT 'my_new_table_temp_2016_06_31' FROM DUAL
)
SELECT REGEXP_SUBSTR( val, '^(.*)_([^_]+)_([^_]+)_([^_]+)$', 1, 1, NULL, 1 ) AS COL4,
REGEXP_SUBSTR( val, '^(.*)_([^_]+)_([^_]+)_([^_]+)$', 1, 1, NULL, 2 ) AS COL3,
REGEXP_SUBSTR( val, '^(.*)_([^_]+)_([^_]+)_([^_]+)$', 1, 1, NULL, 3 ) AS COL2,
REGEXP_SUBSTR( val, '^(.*)_([^_]+)_([^_]+)_([^_]+)$', 1, 1, NULL, 4 ) AS COL1
FROM t
您甚至可以通过使用以下命令使正则表达式简单得多:
You could even make the regular expression much simpler by just using:
'^(.+)_(.+)_(.+)_(.+)$'
第一个.+
是贪婪的,因此它将尽可能匹配,直到第二到第四个捕获组中的最小匹配项所剩字符串不足为止.
The first .+
is greedy so it will match as much as possible until there is only enough of the string left for the minimum matches on the 2nd - 4th capturing groups.
但是,您不需要正则表达式:
WITH t ( VAL ) AS (
SELECT 'my_new_table_2015_06_31' FROM DUAL UNION ALL
SELECT 'my_new_table_temp_2016_06_31' FROM DUAL
)
SELECT SUBSTR( val, 1, pos1 - 1 ) AS col4,
SUBSTR( val, pos1 + 1, pos2 - pos1 - 1 ) AS col3,
SUBSTR( val, pos2 + 1, pos3 - pos2 - 1 ) AS col2,
SUBSTR( val, pos3 + 1 ) AS col1
FROM (
SELECT val,
INSTR( val, '_', -1, 1 ) AS pos3,
INSTR( val, '_', -1, 2 ) AS pos2,
INSTR( val, '_', -1, 3 ) AS pos1
FROM t
);
这篇关于Oracle正则表达式从最后一次出现时拆分字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文