Home » SQL & PL/SQL » SQL & PL/SQL » instr help
instr help [message #237956] Wed, 16 May 2007 03:37 Go to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
SELECT 'trk-chennai-mumbai' as  str FROM dual

SELECT  a,b, a-b,c FROM (
SELECT INSTR(str,'-',1,2) AS a,INSTR(str,'-')-1 AS b,INSTR(str,'-',1,2)-INSTR(str,'-')-1  AS c FROM DATA)


a  b  a-b  c
12 3   9   7




i can use same logic a-b & c but iam geting diff answers why?

thanks,
srinivas

[Updated on: Wed, 16 May 2007 03:39]

Report message to a moderator

Re: instr help [message #237960 is a reply to message #237956] Wed, 16 May 2007 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

x-(y-1) = x-y+1 and not x-y-1

Regards
Michel

Re: instr help [message #237961 is a reply to message #237956] Wed, 16 May 2007 03:46 Go to previous messageGo to next message
Alien
Messages: 245
Registered: June 1999
Senior Member
Mathematics.
you have deducted 1 from b already. Then you deduct b from a, so you end up 2 higher.
The correct value is c.

Note:
a = 12
b = 4-1
c= 12-4-1 = 7
a-b = 12-3 = 9

Regards,

Arian

P.S. Sorry Michel. I was typing while you replied already.

[Updated on: Wed, 16 May 2007 03:47]

Report message to a moderator

Re: instr help [message #237967 is a reply to message #237956] Wed, 16 May 2007 03:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
SELECT 'trk-chennai-mumbai' as str FROM dual

What does this have to do with your question?
Surely, you are not trying to subtract mumbai from chennai?

[Edit: typo]

[Updated on: Wed, 16 May 2007 03:58]

Report message to a moderator

Re: instr help [message #237983 is a reply to message #237967] Wed, 16 May 2007 04:57 Go to previous message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi
As arien and michel said but if you still want the output like
A B A-B C
12 3 9 9

then you can try the below query
SELECT  a,b, a-b,c FROM (
SELECT INSTR(str,'-',1,2) AS a,INSTR(str,'-')-1 AS b,INSTR(str,'-',1,2)-(INSTR(str,'-')-1)  AS c FROM data)


Ashu
Previous Topic: err-PLS-00123
Next Topic: selection from more than two tables
Goto Forum:
  


Current Time: Mon Dec 05 19:24:49 CST 2016

Total time taken to generate the page: 0.08017 seconds