Home » SQL & PL/SQL » SQL & PL/SQL » Substr Query Problem
Substr Query Problem [message #259125] Tue, 14 August 2007 08:31 Go to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Hi All,
Can anyone help me regarding this below query:

select level,
length('abcdefghijklmnopqrstuvwxyz') as len,
substr('abcdefghijklmnopqrstuvwxyz',1+(level-1)*6,6) as strPart
from dual
connect by level <= ceil(length('abcdefghijklmnopqrstuvwxyz')/6)
order by level desc;


this return the result as :

LEVEL	LEN	STRPART
5	26	yz
4	26	stuvwx
3	26	mnopqr
2	26	ghijkl
1	26	abcdef


But I want the result as :

LEVEL	LEN	STRPART
5	26	uvwxyz
4	26	opqrst
3	26	ijklmn
2	26	cdefgh
1	26	ab


Regards
Sanka

[Updated on: Tue, 14 August 2007 08:56] by Moderator

Report message to a moderator

Re: Substr Query Problem [message #259134 is a reply to message #259125] Tue, 14 August 2007 08:52 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I had a quick go:
SELECT     LEVEL
         , LENGTH (:thetext) AS len
         , SUBSTR ( :thetext
                  , DECODE( LEVEL
                          , 1, 1
                          , MOD(LENGTH(:thetext), 6) - 5
                          ) + (LEVEL - 1) * 6
                  , DECODE(LEVEL,1, MOD(LENGTH(:thetext), 6), 6)
                  )                                AS strpart
FROM       DUAL
CONNECT BY LEVEL <= CEIL (LENGTH (:thetext) / 6)
ORDER BY   LEVEL DESC
/


I'm sure I'll come up with something better.

MHE
Re: Substr Query Problem [message #259138 is a reply to message #259134] Tue, 14 August 2007 09:01 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

I think this is not bad but it takes much CPU Cost :

ID	PID	Operation	Name	Rows	Bytes	Cost	CPU Cost	IO Cost
0		SELECT STATEMENT		1 	 	3 	6016753 	2 
1	0	  SORT ORDER BY		1 	 	3 	6016753 	2 
2	1	    CONNECT BY WITHOUT FILTERING		  	 	 	 	 
3	2	      FAST DUAL		1 	 	2 	7271 	2 


But thanks for the help.

Cheers
Sanka
Re: Substr Query Problem [message #259142 is a reply to message #259125] Tue, 14 August 2007 09:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Using one feature of substr, you can use:
SQL> with data as (select 'abcdefghijklmnopqrstuvwxyz' line from dual)
  2  select level, length(line) as len,
  3         substr(line,
  4                greatest(-level*6,-length(line)),
  5                least(6,length(line)-6*(level-1))) as strPart
  6  from data
  7  connect by level <= ceil(length(line)/6)
  8  order by level;
     LEVEL        LEN STRPART
---------- ---------- --------------------------
         1         26 uvwxyz
         2         26 opqrst
         3         26 ijklmn
         4         26 cdefgh
         5         26 ab

5 rows selected.

Regards
Michel
Re: Substr Query Problem [message #259153 is a reply to message #259142] Tue, 14 August 2007 09:29 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

In Maheer's query I'm not getting the STRPART for the value whose length is six, but in Michel's query I'm also getting that one, so I would have to choose the Michel's query for the result.

Cheers
Sanka
Re: Substr Query Problem [message #259169 is a reply to message #259153] Tue, 14 August 2007 09:59 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think it is just a matter of adjusting.
I had to add greatest/least for making it work on the last line.
You can do the same with Maarten's query.

Regards
Michel
Previous Topic: add two dates
Next Topic: How to display message using default lock in ORACLE 9i
Goto Forum:
  


Current Time: Wed Dec 07 22:45:43 CST 2016

Total time taken to generate the page: 0.08829 seconds