Home » SQL & PL/SQL » SQL & PL/SQL » instr help
instr help Wed, 16 May 2007 03:37
 pavuluri Messages: 247Registered: 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
 Michel Cadot Messages: 65155Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 Alien Messages: 253Registered: 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
 Frank Messages: 7880Registered: 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
 ab_trivedi Messages: 460Registered: 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: Difference between Clustered index and non clustered index Next Topic: number to word conversion.
Goto Forum:

Current Time: Thu Aug 24 02:02:14 CDT 2017

Total time taken to generate the page: 0.02351 seconds