How to get the comma separated values into rows like one below another oracle apex

Abinnaya 0
Viewed 36

I have a apex item with values like Value1,Value2,Value3, so on i need to show the values like below

Value 1

Value 2

Value 3

below is my select query, can anyone help here to show the comma seperated values as one below another ex :: a,b,c,d

i need to see the output as

a b c d

SELECT  AD.EMAIL_ADDRESS recipient
FROM   HES_EXTERNAL_ACCOUNTS EA,
       HES_ADDRESSES         AD
WHERE  EA.ACCOUNT_CODE       = 'AUTO';

The above query will result the email address like a,b,c,d i need to show in apex item as a b c d

oracle-apex oracle-apex-5.1 oracle11g
2 Answers

While Chris Saxon's post describe many SQL scenarios, and a cool SQL macro, APEX provides a simple tool for splitting strings into rows:

select column_value from table(apex_string.split('a,b,c,d',','));

Result Sequence                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
----------------
a
b
c
d

That table() clause isn't required from a certain db version, perhaps 18c.

But your question isn't completely clear as to the output you need. On one hand it seems like you want to replace commas with spaces

replace('a,b,c,d', ',', ' ')

but then another moment you suggest one of top of the other

replace('a,b,c,d', ',', '<br>')

If displayed as a column in a report, this would need escaping turned off.

userTfd20

If that select returns a single row, then this is a possible solution:

CREATE TABLE emails (email_list) AS (
select 'mickey.mouse@disney.com, homer.simpson@acme.com'
  from dual
);

Table EMAILS created.

  select TRIM(regexp_substr (
           email_list,
           '[^,]+',
           1,
           level
         )) email
  from   emails
  connect by level <= 
    length ( trim ( both ',' from email_list ) ) - 
    length ( replace ( email_list, ',' ) ) + 1;

EMAIL                                          
-----------------------------------------------
mickey.mouse@disney.com
homer.simpson@acme.com

But Chris has already done all the research and blogged about it here. That blog sums up most common cases and a solution in each of those cases.

Koen Lostrie0
Related