Home » SQL & PL/SQL » SQL & PL/SQL » sql query
sql query [message #560870] Tue, 17 July 2012 08:54 Go to next message
solugenix
Messages: 2
Registered: June 2012
Location: hyderabad
Junior Member
i will give input like 123456

this number has to divide
and display like through sql or plsql
1
2
3
4
5
6
Re: sql query [message #560873 is a reply to message #560870] Tue, 17 July 2012 09:00 Go to previous messageGo to next message
BlackSwan
Messages: 23059
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: sql query [message #560874 is a reply to message #560870] Tue, 17 July 2012 09:05 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Try like...
with test as
      (select replace ((select trim(regexp_replace('123456789','(.)','\1 ')) from dual),' ',',') col from dual)
    select regexp_substr(col, '[^,]+', 1, level) result
    from test
    connect by level <= length(regexp_replace(col, '[^,]+')) + 1; 
Re: sql query [message #560876 is a reply to message #560870] Tue, 17 July 2012 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59794
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Solution depends on your version.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

In the end, FEEDBACK to those that help you and thank them (see your previous topic).

Regards
Michel
Re: sql query [message #560877 is a reply to message #560874] Tue, 17 July 2012 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59794
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@muralikri

Why do you put complex thing like:
(select replace ((select trim(regexp_replace('123456789','(.)','\1 ')) from dual),' ',',') col from dual)

when just a simple:
(select regexp_replace('123456789','(.)','\1,') from dual)

does the same thing?

Regards
Michel
Re: sql query [message #560880 is a reply to message #560877] Tue, 17 July 2012 10:43 Go to previous messageGo to next message
Littlefoot
Messages: 19811
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yet another, altmodisch option:
SQL> with test as
  2    (select '123456' col from dual)
  3  select substr(col, level, 1) res
  4  from test
  5  connect by level <= length(col);

RES
----
1
2
3
4
5
6

6 rows selected.

SQL>
Re: sql query [message #560881 is a reply to message #560874] Tue, 17 July 2012 10:45 Go to previous message
Solomon Yakobson
Messages: 2064
Registered: January 2010
Senior Member
muralikri wrote on Tue, 17 July 2012 10:05
Try like...


Don't overcomplicate. You don't need REGEXP for that:

SELECT  SUBSTR('123456',LEVEL,1) VAL
  FROM  DUAL
  CONNECT BY LEVEL <= LENGTH('123456')
/

V
-
1
2
3
4
5
6

6 rows selected.

SQL> 


SY.
Previous Topic: Exception Handling
Next Topic: select - update pl/sql sql
Goto Forum:
  


Current Time: Wed Nov 26 20:57:30 CST 2014

Total time taken to generate the page: 0.09389 seconds