Geek Answers Handbook

Regular expression data conversion in oracle sql

I have data, as shown below, with a tab limited among them. I introduced them here to see here

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)

My expected result

 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

I thought about replacing all these horizontal tables with some unique patterns like this, and then replacing this pattern with Mon, Tue based on indexing

$ 1 (6-20) $ 2 (6-20) $ 3 (6-20) $ 4 (6-20) $ 5 (6-20)

I tried the following query but could not complete it.

 select regexp_replace(col, '([[:digit:]]-[[:digit:]]{2}[[:space:]]+)','$(\1)') from t_view;
+6
source share
5 answers

You need a combination of the expression CASE , REGEXP_COUNT and REGEXP_REPLACE , since you do not have the same expression for all rows. Depending on the data in the case expression, you can have as many conditions as possible.

Regular expression pattern (\d-\d+ ) .

For instance,

 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>
+3
source

SQL Fiddle

Oracle 11g R2 schema setup :

Request 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

Results :

 | 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 |
+2
source

Why can't we use this simple way? Looks good, like for me

 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

Obviously, it's easy to exclude an empty Sat Sun, for example with 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

You should keep in mind that this is just an example, and if you can receive data with any skipped days, you should use nvl2 in more places.

+1
source

Considering Space as a separator, add to the lines (using levels) and join with LISTAGG() , using the level as the day generator ( TO_CHAR(TRUNC(SYSDATE,'D')+level )

 with t_view as ( select '6-21 6-21 6-21 6-21 6-21 6-21 6-21 ' as col from dual union all select '6-20 6-20 6-20 6-20 6-20 ' from dual union all select '6-9 6-9 6-9 6-9 6-9 6-9 6-9 6-9 ' from dual ) SELECT LISTAGG(TO_CHAR(TRUNC(SYSDATE,'D')+level1,'Dy')||': '|| REGEXP_SUBSTR(col,'[^ ]+',1,LEVEL1),', ') WITHIN GROUP (ORDER BY level1 ) from ( SELECT col,level level1 FROM t_view CONNECT BY REGEXP_SUBSTR(col,'[^ ]+',1,LEVEL) IS NOT NULL AND PRIOR col = col AND PRIOR sys_guid() IS NOT NULL ) group by col;
+1
source

This is my attempt. This is not too different from the MTo version. The idea is the same: convert strings to strings, add bottom information, then rearrange the record.

 with week as ( select 1 day_num, 'Mon' day_name from dual union all select 2 day_num, 'Tue' day_name from dual union all select 3 day_num, 'Wed' day_name from dual union all select 4 day_num, 'Thu' day_name from dual union all select 5 day_num, 'Fri' day_name from dual union all select 6 day_num, 'Sat' day_name from dual union all select 7 day_num, 'Sun' day_name from dual ), t_view as (select '6-21 6-21 6-21 6-21 6-21 6-21 6-21 ' as col from dual union all select '6-20 6-20 6-20 6-20 6-20 ' from dual union all select '6-9 6-9 6-9 6-9 6-9 6-9 6-9 ' from dual ), lines as( select col, WEEK.DAY_NAME, l, trim(regexp_substr(col, '[^,]+', 1, L)) elem from ( select regexp_replace(col,'([[:digit:]]-[[:digit:]]{1,2}[[:space:]]+)','\1,') col from t_view ) join (select level l from dual connect by level < 10) on instr(col, ',', 1, L ) > 0 join week on WEEK.DAY_NUM = l order by col,l ) select listagg(day_name||':'||elem,' ') within group (order by l) from lines group by col;

Result:

 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
+1
source

More articles:


All Articles