converting the data with regexp in oracle sql

Question

I have a data like below with tab limited among them. I have represented them with a view 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 output is

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 of replacing all those horizontal table with some unique patterns like this and then replace that pattern with Mon, Tue based on indexing

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

I have tried the below query but could not complete it

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

Answer 1

Why can't we use this simple way? Looks good as 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 eliminate 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 example, and if you can get data with any number of day missed you should use nvl2 in more places

Answer 2

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

The regular expression pattern is (\d-\d+ ).

For example,

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>

Answer 3

This is my try. It is not different too much from MTo's version. The ideea is the same: transform strings into lines, add information about day, then regroup 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

Answer 4

Considering Space as delimiter, tokenise into rows(using levels) and rejoin using LISTAGG() , using the level as 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;

Answer 5

SQL Fiddle

Oracle 11g R2 Schema Setup:

Query 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 |