CODEWITHSUNDEEP
oracle-databaseoracle-apex
charlie tsai
charlie tsai

Reputation: 285

How to add new line separator into data for oracle

I'm working on displaying data from oracle.
is there a way to make the following data inside the table:

example :

'1.somedata, 2.somedata, 3.somedata, 4.somedata, 5.somedata'

to display like:

example:

'1. somedata
2. somedata
3. somedata
4. somedata
5. somedata'

on the interface?

do i add new line separator directly into the data?

or do i separator them into new line when i query it?

or is there any other simple way?

Thanks.

Upvotes: 0

Views: 5354

Answers (4)

Turntablez
Turntablez

Reputation: 131

I personally would use the listagg function and use '
' as the delimiter.

SELECT LISTAGG(last_name, '
') WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list", MIN(hire_date) "Earliest" FROM employees WHERE department_id = 30;

Upvotes: 1

eaolson
eaolson

Reputation: 15094

Remember that Apex is generating a web page, which means the end result is HTML. Apex, however, will also sometimes escape special HTML characters for you, like < and &. Since you're viewing a table, I assume the source of your data is a query and your "somedata" field is a single column. Try this:

SELECT REPLACE( somedata_column, ',', '<br />' )
  FROM mytable

You don't say what version of Apex. In Apex 4.x, the column would need to be set to a Standard Report Column, which would stop Apex from the <br> elements. I forget what the column type is in Apex 5.x.

Upvotes: 0

Amol
Amol

Reputation: 428

Check below sample query which converts coma separated list data into rows 

SELECT substr( '1.AL,2.AL,3.AL,4.AL,5.AL,6.AL,',
                               ( case when rownum = 1      then 1
                                      else instr( '1.AL,2.AL,3.AL,4.AL,5.AL,6.AL,', ',', 1, rownum - 1 ) + 1 
                                  end ),

                                instr( substr( '1.AL,2.AL,3.AL,4.AL,5.AL,6.AL,',
                                                                     ( case when rownum = 1    then 1
                                                                            else instr( '1.AL,2.AL,3.AL,4.AL,5.AL,6.AL,', ',', 1, rownum - 1 ) + 1 
                                                                        end )
                                ), ',' ) - 1

            ) as data
FROM dual
CONNECT BY LEVEL <= length( '1.AL,2.AL,3.AL,4.AL,5.AL,6.AL,' ) - length ( replace('1.AL,2.AL,3.AL,4.AL,5.AL,6.AL,', ',') )

Hope this will help you!

Upvotes: 0

Brian Leach
Brian Leach

Reputation: 2101

There are so many ways to do this, here is one if you are selecting from a column:

 SELECT REPLACE ('1.somedata, 2.somedata, 3.somedata, 4.somedata, 5.somedata', ',', CHR (13) || CHR (10)) AS split
      FROM DUAL;
1.somedata
 2.somedata
 3.somedata
 4.somedata
 5.somedata

Upvotes: 1

Related Questions