OraFAQ Forum: SQL & PL/SQL » retreive the first and third segment of a value

Home » SQL & PL/SQL » SQL & PL/SQL » retreive the first and third segment of a value

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

retreive the first and third segment of a value [message #2608]

Tue, 30 July 2002 21:08

Gopi
Messages: 8
Registered: July 2002

Junior Member

I want to display the first segment of a value. the value is like this 1234:abcd:efgh. I want to retreive the first segment ie. 1234 and the last segment ie. efgh. But some of the values have only one segment ie. only 1234. so if the value has only one segment, I'll have to display the first segment and if it has three segments, then I'll have to display the first and third segment. Can anyone help me out on this.

Regards
Gopi

Report message to a moderator

Re: retreive the first and third segment of a value [message #2609 is a reply to message #2608]

Tue, 30 July 2002 22:23

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Hi,
This might help you out:

select substr('&test',1, decode(instr('&test',':',1,1),0,length('&test'),instr('&test',':',1,1)-1)) First
     , substr('&test', instr('&test',':',1,2)+1,decode(instr('&test',':',1,1),0,0,length('&test')-instr('&test',':',1,2)+1)) Third
  from dual
/

If you ommit the decode statements, the query will return the value of the segment in the Third Segment when the field contains only one segment. I'm sure there are more beautiful solutions, but this was the first I could come up with... .
Anyway, play around with decode, INSTR and SUBSTR...

HTH,
MHE

Report message to a moderator

Previous Topic:	fetching the next and previous column value for the current record
Next Topic:	Passing parameter from the command line

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 01:20:44 CDT 2024