Apex 5 display shuttle stored data delimted by semicolon, in list form

Question

as the title says. I have shuttle data saved in a column seperated by a semicolon:

confid | values
------------------------------------------------------
id1    | conf_value1;conf_value2;conf_value3;conf_value4

The reason it is store as this is because not one configuration is the same and can have anything between a single config value, to 50. So creating seperate columns for each is difficult.

I need to however find a way to display this in Apex in a list form and not semicolon seperated. So simply doing :

select values from table where confid='id1'

simply returns:

conf_value1;conf_value2;conf_value3;conf_value4

which is difficult to read by the users..

So the question, is there a way to display the data in a list form in Apex instead? something like when you do the search it will display:

confid | values
---------------------
id1    | conf_value1
         conf_value2
         conf_value3
         conf_value4

Or does Apex only return data in table like styles?

Answer 1

Split those values into rows. Here's an example; CTE is here to provide test data. You need part that follows the comment.

SQL> with test (confid, cvalues) as
  2    (select 'id1',
  3            'conf_value1;conf_value2;conf_value3;conf_value4'
  4     from dual)
  5  -- this is what you need
  6  select regexp_substr(cvalues, '[^;]+', 1, level) value
  7  from test
  8  connect by level <= regexp_count(cvalues, ';') + 1;

VALUE
--------------------------------------------------------------------
conf_value1
conf_value2
conf_value3
conf_value4

SQL>