在oracle sql中用regexp转换数据
在oracle sql中用regexp转换数据
提问于 2015-09-07 08:27:12
我有像下面这样的数据，标签被限制在其中。我在这里代表了他们
with t_view as (select '6-21 6-21 6-21 6-21 6-21 6-21 6-21 ' as col from dual
union
select '6-20 6-20 6-20 6-20 6-20 ' from dual
union
select '6-9 6-9 6-9 6-9 6-9 6-9 6-9 ' from dual)我的预期产出是
Mon: 6-21, Tue: 6-21, Wed: 6-21, Thu: 6-21, Fri: 6-21, Sat: 6-21, Sun: 6-21
Mon: 6-20, Tue: 6-20, Wed: 6-20, Thu: 6-20, Fri: 6-20
Mon: 6-9, Tue: 6-9, Wed: 6-9, Thu: 6-9, Fri: 6-9, Sat: 6-9, Sun: 6-9我想用像这样的一些独特的模式替换所有的水平表，然后基于索引用Mon，Tue替换那个模式。
$1(6-20 )$2(6-20 )$3(6-20 )$4(6-20 )$5(6-20 )
我尝试过以下查询，但无法完成它。
select regexp_replace(col, '([[:digit:]]-[[:digit:]]{2}[[:space:]]+)','$(\1)') from t_view;回答 5
Stack Overflow用户
发布于 2015-09-07 08:58:28
您需要将REGEXP_REPLACE、CASE表达式、REGEXP_COUNT和组合起来，因为对于所有行，您都没有相同的表达式。根据数据，您可以在case表达式中拥有同样多的条件。
正则表达式模式是(\d-\d+ )。
例如,
SQL> WITH t_view(col) AS
2 ( SELECT '6-21 6-21 6-21 6-21 6-21 6-21 6-21 ' FROM dual
3 UNION
4 SELECT '6-20 6-20 6-20 6-20 6-20 ' FROM dual
5 UNION
6 SELECT '6-9 6-9 6-9 6-9 6-9 6-9 6-9 ' FROM dual
7 )
8 SELECT REPLACE(new_col, ' ','') new_col
9 FROM (
10 SELECT
11 CASE
12 WHEN regexp_count(col, '\d+\-\d+') = 5
13 THEN regexp_replace(col,
14 '(\d-\d+ )(\d-\d+ )(\d-\d+ )(\d-\d+ )(\d-\d+ )',
15 'Mon: \1, Tue: \2,Wed: \3,Thu: \4,Fri: \5')
16 WHEN regexp_count(col, '\d+\-\d+') = 7
17 THEN regexp_replace(col,
18 '(\d-\d+ )(\d-\d+ )(\d-\d+ )(\d-\d+ )(\d-\d+ )(\d-\d+ )(\d-\d+ )',
19 'Mon: \1, Tue: \2,Wed: \3,Thu: \4,Fri: \5,Sat: \6,Sun: \7')
20 END new_col
21 FROM t_view
22 );
NEW_COL
----------------------------------------------------------------------------------------------------
Mon: 6-20, Tue: 6-20,Wed: 6-20,Thu: 6-20,Fri: 6-20
Mon: 6-21, Tue: 6-21,Wed: 6-21,Thu: 6-21,Fri: 6-21,Sat: 6-21,Sun: 6-21
Mon: 6-9, Tue: 6-9,Wed: 6-9,Thu: 6-9,Fri: 6-9,Sat: 6-9,Sun: 6-9
SQL>Stack Overflow用户
发布于 2015-09-07 09:02:13
SQL Fiddle
Oracle 11g R2架构设置
查询1
with t_view ( col ) as (
select '6-21 6-21 6-21 6-21 6-21 6-21 6-21 ' from dual
union select '6-20 6-20 6-20 6-20 6-20 ' from dual
union select '6-9 6-9 6-9 6-9 6-9 6-9 6-9 6-9 6-9' from dual
union select '6-1' from dual
union select '6-1 6-2' from dual
),
days ( id, day ) AS (
SELECT 1, 'Mon' FROM DUAL
UNION ALL SELECT 2, 'Tue' FROM DUAL
UNION ALL SELECT 3, 'Wed' FROM DUAL
UNION ALL SELECT 4, 'Thu' FROM DUAL
UNION ALL SELECT 5, 'Fri' FROM DUAL
UNION ALL SELECT 6, 'Sat' FROM DUAL
UNION ALL SELECT 0, 'Sun' FROM DUAL
),
matches ( col, idx, day ) AS (
SELECT col,
COLUMN_VALUE,
day || ': ' || REGEXP_SUBSTR( t.col, '\d+-\d+', 1, COLUMN_VALUE )
FROM t_view t,
TABLE(
CAST(
MULTISET(
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT( t.col, '\d+-\d+' )
)
AS SYS.ODCINUMBERLIST
)
) l
INNER JOIN days d
ON ( MOD( l.COLUMN_VALUE, 7 ) = d.id )
)
SELECT LISTAGG( day, ', ' ) WITHIN GROUP ( ORDER BY IDX ) AS col
FROM matches
GROUP BY col结果
| COL |
|------------------------------------------------------------------------------------------|
| Mon: 6-1 |
| Mon: 6-1, Tue: 6-2 |
| Mon: 6-20, Tue: 6-20, Wed: 6-20, Thu: 6-20, Fri: 6-20 |
| Mon: 6-21, Tue: 6-21, Wed: 6-21, Thu: 6-21, Fri: 6-21, Sat: 6-21, Sun: 6-21 |
| Mon: 6-9, Tue: 6-9, Wed: 6-9, Thu: 6-9, Fri: 6-9, Sat: 6-9, Sun: 6-9, Mon: 6-9, Tue: 6-9 |Stack Overflow用户
发布于 2015-09-07 08:54:35
为什么我们不能用这种简单的方法？对我来说很好看
SELECT 'Mon: '||regexp_substr(col,'\d+\-\d+',1,1) ||
', Tue: '||regexp_substr(col,'\d+\-\d+',1,2) ||
', Wed: '||regexp_substr(col,'\d+\-\d+',1,3) ||
', Thu: '||regexp_substr(col,'\d+\-\d+',1,4) ||
', Fri: '||regexp_substr(col,'\d+\-\d+',1,5) ||
', Sat: '||regexp_substr(col,'\d+\-\d+',1,6) ||
', Sun: '||regexp_substr(col,'\d+\-\d+',1,7)
FROM t_view显然，消除空Sun很容易，例如，使用nvl2：
SELECT 'Mon: '||regexp_substr(col,'\d+\-\d+',1,1) ||
', Tue: '||regexp_substr(col,'\d+\-\d+',1,2) ||
', Wed: '||regexp_substr(col,'\d+\-\d+',1,3) ||
', Thu: '||regexp_substr(col,'\d+\-\d+',1,4) ||
', Fri: '||regexp_substr(col,'\d+\-\d+',1,5) ||
nvl2(regexp_substr(col,'\d+\-\d+',1,6),
', Sat: '||regexp_substr(col,'\d+\-\d+',1,6) ||
', Sun: '||regexp_substr(col,'\d+\-\d+',1,7),null)
FROM t_view您应该记住，这只是一个例子，如果您可以在错过的天数内获得数据，那么您应该在更多的地方使用nvl2。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/32434173
复制相关文章
相似问题
腾讯云开发者
