oracle user defined delimiter in sql

Question

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.

Not the same, but similar: A question I asked a while ago. The answer, unfortunately, wasn't very satisfying: http://stackoverflow.com/questions/11454136/oracle-concat-with-delimiter-but-only-if-both-operands-are-not-null — GolezTrol, Jun 16 '14 at 11:47

San · Accepted Answer · 2014-06-16T12:31:14.680

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 |

Maheswaran Ravisankar · Answer 2 · 2014-06-16T13:16:36.780

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;

score 1 · Answer 3 · answered Jun 16 '14 at 12:52

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

Thats a great attempt, using simple function available since oracle 8i. The same concept I did it _already_ using combination of `regexp_count()` and `regexp_replace()` , making use of the database's latest functions. — Maheswaran Ravisankar, Jun 16 '14 at 12:59
Good try @ChrisProsser, I think OracleUser and San made query little simple. — ajmalmhd04, Jun 16 '14 at 13:01
@OracleUser Your answer does not cater for all potential inputs, if you copy the first with statement from the above and run with the rest of your code you will see that around half of the 16 possible inputs get an `and` straggling at the end of the string. — ChrisProsser, Jun 16 '14 at 13:08
@OracleUser Aside from that I am not disputing that your version is more concise. — ChrisProsser, Jun 16 '14 at 13:09
@ChrisProsser yes, you were right, I missed the trailing comma. Fixed it now! — Maheswaran Ravisankar, Jun 16 '14 at 13:17

oracle user defined delimiter in sql

3 Answers3