Home » SQL & PL/SQL » SQL & PL/SQL » How to get these strings each in a line (Oracle 10g ,win XP)
How to get these strings each in a line [message #384714] Wed, 04 February 2009 23:17 Go to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member


I have a string like this ,How to get these strings each in a line
delimited by comma (',').how to do this

1W0130B15,1W0130A23,1W0120A02,1W0130A33,1W0130A05,1W0130A32,1W0130A16,1W0120A33,1W0120A32,
1W0130A02,1W0130B03,1W0130B02,1W0130A11,1W0120A31,1W0120A17,1W0120A16,1W0120A01,1W0130A30,
1W0130A29,1W0120A15,1W0130A24,1W0130A22,1W0120A12,1W0140A05,1W0140A06,1W0140A04,1W0140A03,
1E030N,2A031N,2A030N,1W0010A11,1W0010B01,1W0010A02,1W0010B22,1W0010B19,1W0010B24,1W0010B27,
1W0010A12,1W0010B18,1W0010B15,1W0010B13,1W0010B20,1W0010B17,1W0010B16,1W0010B14,1W0010A01,



Thanks in Advance
Re: How to get these strings each in a line [message #384715 is a reply to message #384714] Wed, 04 February 2009 23:19 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: How to get these strings each in a line [message #384721 is a reply to message #384715] Thu, 05 February 2009 00:00 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Here in my case it is the data which i get from vb.net ,so i want to work with this data .
Re: How to get these strings each in a line [message #384724 is a reply to message #384714] Thu, 05 February 2009 00:04 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Here in my case it is the data which i get from vb.net ,so i want to work with this data
So proceed to do so.

Enjoy!
Re: How to get these strings each in a line [message #384726 is a reply to message #384721] Thu, 05 February 2009 00:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It all depends on what you want to do with it. Do you want it displayed as if they were on separate lines (replace commas by chr(13)) or do you want a resultset (search for varying in list)
Re: How to get these strings each in a line [message #384734 is a reply to message #384726] Thu, 05 February 2009 00:23 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Is it what you are looking for

SQL> COLUMN RESULT FOR A20
SQL> WITH tab AS
     (
        SELECT '1W0130B15,1W0130A23,1W0120A02,1W0130A33,1W0130A05,1W0130A32,1W0130A16,1W0120A33,1W0120A32'
                                                                          str
          FROM DUAL)
SELECT     REGEXP_SUBSTR (str, '[^,]+', 1, LEVEL) RESULT
      FROM tab
CONNECT BY LEVEL <=
              ((SELECT     SUM (CASE
                                   WHEN SUBSTR (str, LEVEL, 1) = ','
                                      THEN 1
                                   WHEN LENGTH (str) = LEVEL
                                      THEN 1
                                   ELSE 0
                                END
                               )
                      FROM tab
                CONNECT BY LEVEL <= LENGTH (str)))

RESULT              
--------------------
1W0130B15           
1W0130A23           
1W0120A02           
1W0130A33           
1W0130A05           
1W0130A32           
1W0130A16           
1W0120A33           
1W0120A32           

9 rows selected.


Thanks
Trivendra
Re: How to get these strings each in a line [message #384749 is a reply to message #384734] Thu, 05 February 2009 00:56 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Great ,

Thanks Trivendra it work fine
Re: How to get these strings each in a line [message #384753 is a reply to message #384714] Thu, 05 February 2009 01:01 Go to previous messageGo to next message
Jothish
Messages: 22
Registered: February 2009
Location: Chennai
Junior Member
Hi Friend,Try this Query
SELECT REPLACE('1W0130B15,1W0130A23,1W0120A02,1W0130A33,1W0130A05,1W0130A32,1W0130A16,1W0120A33,1W0120A32,1W0130A02,1W0130B03,1W0130B02,1W01 30A11,1W0120A31,1W0120A17,1W0120A16,1W0120A01,1W0130A30,1W0130A29,1W0120A15,1W0130A24,1W0130A22,1W0120A12,1W0140A05,1W0140A06,1W0140A 04,1W0140A03,1E030N,2A031N,2A030N,1W0010A11,1W0010B01,1W0010A02,1W0010B22,1W0010B19,1W0010B24,1W0010B27,1W0010A12,1W0010B18,1W0010B15 ,1W0010B13,1W0010B20,1W0010B17,1W0010B16,1W0010B14,1W0010A01',',',CHR(10)) FROM DUAL

are you Expecting like this Given O/p:
1W0130B15
1W0130A23
1W0120A02
1W0130A33
1W0130A05
Then try above Query,
Thanks ,
Jothish. Shocked
Re: How to get these strings each in a line [message #384988 is a reply to message #384753] Fri, 06 February 2009 00:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Wow, you thought of this all by yourself?
Re: How to get these strings each in a line [message #385156 is a reply to message #384988] Fri, 06 February 2009 12:13 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
The big difference in solutions is 3 rows returned vs 1 row. The goal is almost always to return 1 row for each value...
SQL> set feedback 1
SQL>
SQL> WITH tab AS (
  2          SELECT 'A,B,C' str
  3            FROM DUAL)
  4  SELECT     REGEXP_SUBSTR (str, '[^,]+', 1, LEVEL) RESULT
  5        FROM tab
  6  CONNECT BY LEVEL <=
  7    ((SELECT     SUM (CASE
  8                       WHEN SUBSTR (str, LEVEL, 1) = ',' THEN 1
  9                       WHEN LENGTH (str) = LEVEL THEN 1  ELSE 0
 10                      END)
 11     FROM tab
 12      CONNECT BY LEVEL <= LENGTH (str)));

RESUL
-----
A
B
C

3 rows selected.

SQL> SELECT REPLACE('A,B,C',',',CHR(10)) FROM DUAL;

REPLA
-----
A
B
C


1 row selected.

SQL>
Previous Topic: deleting with a join
Next Topic: overlap interval
Goto Forum:
  


Current Time: Sun Dec 04 21:01:09 CST 2016

Total time taken to generate the page: 0.10903 seconds