• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 564
  • Last Modified:

Oracle SQL

The data stored in the column as follows


Remarks         - Column Name
---------------
1234567, abcdefghijk,98765            - Data in the column

I want
--------

Remarks
-----------
1234567,
abcdefghijk,
98765

Is it possible ?
0
VenkatBunny
Asked:
VenkatBunny
  • 12
  • 11
1 Solution
 
slightwv (䄆 Netminder) Commented:
Yes.

Code below uses mostly SQL from:

http://nuijten.blogspot.com/2009/07/splitting-comma-delimited-string-regexp.html
drop table tab1 purge;
create table tab1(remarks varchar2(30));

insert into tab1 values('1234567, abcdefghijk,98765');


select trim(regexp_substr (remarks, '[^,]+', 1, rownum)) split 
from tab1 
connect by level <= length (regexp_replace (remarks, '[^,]+'))  + 1
/

Open in new window

0
 
VenkatBunnyAuthor Commented:
I have a problem here, My remarks column is not coming from any table, I created a caiculation column and doing some concatenation
0
 
VenkatBunnyAuthor Commented:
Below is the code which give data as

1234567, abcdefghijk,98765  


select rtrim (xmlagg (xmlelement (e, '('||round(amount,2)||')'||' ' ||name|| ',' ||' ')).extract ('//text()'), ',')
   from tab1

I want to go to next line when there is  comma in the data
1234567,
abcdefghijk,
98765
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
slightwv (䄆 Netminder) Commented:
The code you gave actually creates the csv in the first place.  If you don't want in comma delimited, just don't do it.

Do you want separate rows(3 returned) or just a hard-coded cr/lf?

If that just replace:

Replace(your_output,',',',' || chr(13) || chr(10))
0
 
VenkatBunnyAuthor Commented:
I used the below

Replace(your_output,',',',' || chr(13) || chr(10))


but I'm missing

1234567,
abcdefghijk,
98765 - MISSING THIS DATA
0
 
slightwv (䄆 Netminder) Commented:
Works for me.

drop table tab1 purge;
create table tab1(remarks varchar2(30));

insert into tab1 values('1234567, abcdefghijk,98765');

select Replace(remarks,',',',' || chr(13) || chr(10)) from tab1;


SQL> select Replace(remarks,',',',' || chr(13) || chr(10)) from tab1;
1234567,
 abcdefghijk,
98765


1 row selected.
0
 
slightwv (䄆 Netminder) Commented:
Are you wanting three rows or one row with a crlf?

Still, why create the comma separated list using XMLAGG if you are just going to rip it back apart.  Just create it in the format you want.

If you can provide a simple test case of the original table and values, I'm sure we can provide the final result.

Also, since you are new to Experts-Exchange, if you want quick responses and more Experts involved, you might think about assigning more points to your questions.
0
 
VenkatBunnyAuthor Commented:
sure I will increase points value but still missing some data
0
 
slightwv (䄆 Netminder) Commented:
Post something I can test with.  I've executed everything I have posted and it works for me.

If I can't reproduce what you see, I can't help.
0
 
VenkatBunnyAuthor Commented:
Okay create a table with 3 columns

Tab1
No           Amount        Remarks
--------------------------------------
1              3000              ABC
1              2000              XYZ
1              1000              DEF

What I was doing is Instead of showing 3 lines I used the below sql to show in single line

select rtrim (xmlagg (xmlelement (e, '('||round(amount,2)||')'||' ' ||remarks|| ',' ||' ')).extract ('//text()'), ',')
   from tab1

So Now my output is 300 ABC,2000 XYZ,1000 DEF,   - Everything coming in one lane fine

Now what I want is when ever there is comma( ,) it should split to next line ( It should not create another row)

Please let me know if I'm not clear

0
 
slightwv (䄆 Netminder) Commented:
The code below gives me the following output:

(3000)
ABC
(2000)
XYZ
(1000)
DEF


1 row selected.

drop table tab1 purge;
create table tab1(amount number, remarks char(3));
insert into tab1 values(3000,'ABC');
insert into tab1 values(2000,'XYZ');
insert into tab1 values(1000,'DEF');
commit;


select rtrim(xmlagg (xmlelement (e, '('||round(amount,2)||')'||chr(13)||chr(10) ||remarks|| chr(13)||chr(10))).extract ('//text()'), chr(13)||chr(10))
from tab1
/

Open in new window

0
 
VenkatBunnyAuthor Commented:
I want
----------
(3000)  ABC
(2000)  XYZ
(1000)  DEF
0
 
VenkatBunnyAuthor Commented:
I know it is confusing basically I wan to show All the 3 rows in singe row

second splitting the single row into multiple lines not rows
0
 
slightwv (䄆 Netminder) Commented:
That is not what you said:
"So Now my output is 300 ABC,2000 XYZ,1000 DEF"
"Now what I want is when ever there is comma( ,) it should split to next line"

Based on that, you get what I posted.

That said:

The latest requirements are a simple change:

(3000) ABC
(2000) XYZ
(1000) DEF


1 row selected.



select rtrim(xmlagg (xmlelement (e, '('||round(amount,2)||')'|| ' ' ||remarks|| chr(13)||chr(10))).extract ('//text()'), chr(13)||chr(10))
from tab1
/

Open in new window

0
 
VenkatBunnyAuthor Commented:
I don't know for some reason I'm not getting, I still get one row i.e.

(3000) ABC (2000) XYZ (1000 DEF

I ran the same sql you sent

0
 
slightwv (䄆 Netminder) Commented:
What Oracle version are you using?
What tool are you using?
What OS?

I'm using Windows, sqlplus and version 10.2.0.1.
0
 
VenkatBunnyAuthor Commented:


Same Windows XP, SQL*Plus: Release 10.2.0.1.0
0
 
slightwv (䄆 Netminder) Commented:
Sorry.  It works for me.  If I can't reproduce what you see, I cannot fix it.

Here's my entire run from sql*plus with echo on.


SQL> set echo on
SQL> @q
SQL> drop table tab1 purge;

Table dropped.

SQL> create table tab1(amount number, remarks char(3));

Table created.

SQL> insert into tab1 values(3000,'ABC');

1 row created.

SQL> insert into tab1 values(2000,'XYZ');

1 row created.

SQL> insert into tab1 values(1000,'DEF');

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> 
SQL> select rtrim(xmlagg (xmlelement (e, '('||round(amount,2)||')'|| ' ' ||remarks|| chr(13)||chr(10))).extract ('//text()'), chr(13)||chr(10))
  2  from tab1
  3  /
(3000) ABC
(2000) XYZ
(1000) DEF


1 row selected.

SQL> 
SQL> spool off

Open in new window

output.JPG
0
 
VenkatBunnyAuthor Commented:

Some thing weired from my end.. Anyways thanks for your help I'm closing this post.
0
 
slightwv (䄆 Netminder) Commented:
Check your regional settings.  There might be some something in the way a cr/lf is handled.

Also make sure you don't have some login.sql or glogin.sql that does something weird in your sqlplus.
0
 
VenkatBunnyAuthor Commented:
Okay sure...Thanks for your help slightwv, Appreciated
0
 
slightwv (䄆 Netminder) Commented:
No problem.  Sorry I can't figure it out for you.
0
 
VenkatBunnyAuthor Commented:
Np
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 12
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now