Home » SQL & PL/SQL » SQL & PL/SQL » sql help
sql help [message #234736] Thu, 03 May 2007 01:22 Go to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
i have table like

no
123456

i want to

no
1
12
123
1234
12345
123456

i want to
1
23
456

Re: sql help [message #234742 is a reply to message #234736] Thu, 03 May 2007 01:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And what have you tried?
Re: sql help [message #234744 is a reply to message #234742] Thu, 03 May 2007 01:51 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
Hi Frank,
iam Approaching this way

SELECT SUBSTR('123456',LEVEL,1) AS str FROM dual
CONNECT BY LEVEL <= LENGTH('123456')


is it right way?
help me
Re: sql help [message #234747 is a reply to message #234744] Thu, 03 May 2007 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Correct, just a small change in SUBSTR:
SQL> SELECT SUBSTR('123456',1,LEVEL) AS str FROM dual
  2  CONNECT BY LEVEL <= LENGTH('123456')
  3  /
STR
------
1
12
123
1234
12345
123456

6 rows selected.

Regards
Michel
Re: sql help [message #234749 is a reply to message #234747] Thu, 03 May 2007 02:01 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
hi Michel,

my second reqirement is


i want to
1
23
456




may be Length is more

[Updated on: Thu, 03 May 2007 02:07]

Report message to a moderator

Re: sql help [message #234751 is a reply to message #234749] Thu, 03 May 2007 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just play with substr.
I didn't try it but it seems not difficult to do it.
Just a little bit of maths, doesn't it.

Regards
Michel
Re: sql help [message #234752 is a reply to message #234749] Thu, 03 May 2007 02:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
To do this, you first have to find a way to determine the startposition of your substr, in relation to your rownum
Re: sql help [message #234756 is a reply to message #234752] Thu, 03 May 2007 02:13 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
my mind Blank.
any sugesstion?
may be length problem in previous query. how do change length

thanks
srinivas

Re: sql help [message #234765 is a reply to message #234756] Thu, 03 May 2007 02:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As this is not Oracle/SQL but maths, I give you a solution:
SQL> SELECT SUBSTR('123456789ABCDEF',1,LEVEL) AS str FROM dual
  2  CONNECT BY LEVEL <= LENGTH('123456789ABCDEF')
  3  /
STR
---------------
1
12
123
1234
12345
123456
1234567
12345678
123456789
123456789A
123456789AB
123456789ABC
123456789ABCD
123456789ABCDE
123456789ABCDEF

15 rows selected.

SQL> SELECT SUBSTR('123456789ABCDEF',(LEVEL*LEVEL-LEVEL+2)/2,LEVEL) AS str FROM dual
  2  CONNECT BY LEVEL*(LEVEL+1)/2 <= LENGTH('123456789ABCDEF')
  3  /
STR
---------------
1
23
456
789A
BCDEF

5 rows selected.

Regards
Michel
Re: sql help [message #234772 is a reply to message #234765] Thu, 03 May 2007 02:30 Go to previous message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
Thanks Michel

regards
srinivas
Previous Topic: PROBLEM IN UPDATING GROUP POLICY ora 00904
Next Topic: max count top 4
Goto Forum:
  


Current Time: Sat Dec 03 09:48:32 CST 2016

Total time taken to generate the page: 0.28277 seconds