CODEWITHSUNDEEP
sqloracle-databaseoracle11g
ajmalmhd04
ajmalmhd04

Reputation: 2602

oracle user defined delimiter in sql

I have a sample table which looks like following:

WITH T(ID, SLOW, MEDIUM, FAST, SUPER_FAST) AS
     (SELECT 1, 'Y', 'Y', 'N', 'Y' FROM DUAL
     UNION ALL
     SELECT 2, 'N','Y','N', 'Y' FROM DUAL
     )
SELECT 'Identified in '
||CASE WHEN L_TEXT_A IS NOT NULL THEN L_TEXT_A END ||' , '
||CASE WHEN L_TEXT_B IS NOT NULL THEN L_TEXT_B END ||' , '
||CASE WHEN L_TEXT_C IS NOT NULL THEN L_TEXT_C END ||' & '
||CASE WHEN L_TEXT_D IS NOT NULL THEN L_TEXT_D END
FROM
(
SELECT CASE WHEN slow = 'Y' THEN 'slow' END L_TEXT_A,
     CASE WHEN medium = 'Y' THEN 'medium' END L_TEXT_B,
     CASE WHEN FAST = 'Y' THEN 'fast' END L_TEXT_C,
     case when SUPER_FAST = 'Y' then 'super fast' END L_TEXT_D
FROM T
); --

Identified in slow , medium ,  & super fast                                                                                                                                                                                                                      
Identified in  , medium ,  & super fast

I need to get a result which should return as like:

'Identified in slow, medium and super fast'
'Identified in medium and super fast'

The condition is no delimiter for single column which having Y , ampersand for two 'Y' values, and commas followed by ampersand which having more than 2 columns.

Upvotes: 2

Views: 124

Answers (3)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

The below query delimits as you expected..

The Key is last occurrence of comma is replaced by AND.
regexp_count is used to find the last occurrence.

SQL> select regexp_replace('Identified in slow , medium , super fast',
               ',' ,' and ',1,
               regexp_count('Identified in slow , medium , super fast',',')) from dual;  2    3

REGEXP_REPLACE('IDENTIFIEDINSLOW,MEDIUM,SUPE
--------------------------------------------
Identified in slow , medium  and  super fast

Full Version:

 WITH T(ID, SLOW, MEDIUM, FAST, SUPER_FAST) AS
     (SELECT 1, 'Y', 'Y', 'N', 'Y' FROM DUAL
     UNION ALL
     SELECT 2, 'N','Y','N', 'Y' FROM DUAL
     ),
mytext as 
(SELECT trim(trailing ',' FROM 'Identified in '
||NVL2(L_TEXT_A,L_TEXT_A||',',NULL)
||NVL2(L_TEXT_B,L_TEXT_B||',',NULL)
||NVL2(L_TEXT_C,L_TEXT_C||',',NULL)
||(L_TEXT_D)) as text
FROM
(
SELECT CASE WHEN slow = 'Y' THEN 'slow' END L_TEXT_A,
     CASE WHEN medium = 'Y' THEN 'medium' END L_TEXT_B,
     CASE WHEN FAST = 'Y' THEN 'fast' END L_TEXT_C,
     case when SUPER_FAST = 'Y' then 'super fast' END L_TEXT_D
FROM T
)
)
SELECT regexp_replace(text,
               ',' ,
               ' and ',
               1,
               regexp_count(text,',')) FROM mytext;

Upvotes: 1

ChrisProsser
ChrisProsser

Reputation: 13098

Here is some code that uses simple instr, substr and replace functions built in functions which seems to work for all combinations. Note, if you have the option of doing this via a PL/SQL function you could probably simplify the code a bit:

with t(id, slow, medium, fast, super_fast) as (
   select 1, 'n', 'n', 'n', 'n' from dual
   union all
   select 2, 'y', 'n', 'n', 'n' from dual
   union all
   select 3, 'n', 'y', 'n', 'n' from dual
   union all
   select 4, 'n', 'n', 'y', 'n' from dual
   union all
   select 5, 'n', 'n', 'n', 'y' from dual
   union all
   select 6, 'y', 'y', 'n', 'n' from dual
   union all
   select 7, 'y', 'n', 'y', 'n' from dual
   union all
   select 8, 'y', 'n', 'n', 'y' from dual
   union all
   select 9, 'n', 'y', 'y', 'n' from dual
   union all
   select 10, 'n', 'y', 'n', 'y' from dual
   union all
   select 11, 'n', 'n', 'y', 'y' from dual
   union all
   select 12, 'y', 'y', 'y', 'n' from dual
   union all
   select 13, 'y', 'y', 'n', 'y' from dual
   union all
   select 14, 'y', 'n', 'y', 'y' from dual
   union all
   select 15, 'n', 'y', 'y', 'y' from dual
   union all
   select 16, 'y', 'y', 'y', 'y' from dual
   ),
   step1 as (
   select 'identified in ' ||
          decode(slow,'y', 'slow' || ', ', '') ||
          decode(medium,'y', 'medium' || ', ', '') ||
          decode(fast,'y', 'fast' || ', ', '') ||
          decode(super_fast,'y', 'super fast' || ', ', '') str
     from t
   ),
   step2 as (
   select length(str) - length(replace(str, ',', null)) as vals, -- count values (using commas)
          substr(str, 1, length(str)-2) as str -- strip final comma
     from step1
   ),
   step3 as (
   select str,
          decode(vals, 0, 0, 1, 0, instr(str, ',', 1, vals -1)) as final_comma_pos,
          vals
     from step2
   ),
   step4 as (
   select decode(vals, 0, null,
                       1, str,
                          substr(str, 1, final_comma_pos - 1) || ' &' || 
                          substr(str, final_comma_pos + 1)
                ) as str
     from step3
   )
select * from step4;

Output:

identified in slow
identified in medium
identified in fast
identified in super fast
identified in slow & medium
identified in slow & fast
identified in slow & super fast
identified in medium & fast
identified in medium & super fast
identified in fast & super fast
identified in slow, medium & fast
identified in slow, medium & super fast
identified in slow, fast & super fast
identified in medium, fast & super fast
identified in slow, medium, fast & super fast

Upvotes: 1

San
San

Reputation: 4538

If you are using Oracle 11g2, a combination of unpivot and listagg can help, try this query

WITH T(ID, SLOW, MEDIUM, FAST, SUPER_FAST) AS
     (SELECT 1, 'Y', 'Y', 'N', 'Y' FROM DUAL
     UNION ALL
     SELECT 2, 'N','Y','N', 'Y' FROM DUAL
     union all
     SELECT 3, 'N','N','N', 'Y' FROM DUAL
     union all 
     SELECT 4, 'N','N','N', 'N' FROM DUAL
     ),
tlist as (select listagg(speed, ', ') within group (order by rownum) as list, id
           from T
         unpivot (pace for speed in (SLOW as 'Slow',MEDIUM as 'Medium',  FAST as 'Fast', SUPER_FAST as 'Super Fast'))
          where pace = 'Y'
          group by id)
select case when instr(list, ', ', -1, 1) = 0 then 
            list
       else
            substr(list, 1, instr(list, ', ', -1, 1) - 1) || ' and ' || substr(list, instr(list, ', ', -1, 1) + 2)  
       end as  list
  from tlist;

output:

|                        LIST |
|-----------------------------|
| Slow, Medium and Super Fast |
|       Medium and Super Fast |
|                  Super Fast |

Upvotes: 3

Related Questions