OraFAQ Forum: SQL & PL/SQL » extract character between points

Home » SQL & PL/SQL » SQL & PL/SQL » extract character between points

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

extract character between points [message #632823]

Fri, 06 February 2015 02:42

mmm286
Messages: 14
Registered: July 2014

Junior Member

Hi,
I have these string:

make.23.6789.323424.000.ksh
do.1.2342.skdkss.212ksh

I would like to extract the characters always between 3 and 4 point (323424 and skdkss).
How could I do these. I've tried with instr but I cant do it.
Many thanks!

Report message to a moderator

Re: extract character between points [message #632826 is a reply to message #632823]

Fri, 06 February 2015 02:45

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

well instr and substr are what you need, so what exactly did you try?

Report message to a moderator

Re: extract character between points [message #632829 is a reply to message #632826]

Fri, 06 February 2015 02:51

mmm286
Messages: 14
Registered: July 2014

Junior Member

cookiemonster wrote on Fri, 06 February 2015 02:45

well instr and substr are what you need, so what exactly did you try?

select (substr(xxxx,instr(xxxx, '.',1,3)+1,3)) xxxxx from yyyyyy;

Report message to a moderator

Re: extract character between points [message #632831 is a reply to message #632829]

Fri, 06 February 2015 02:58

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

You're part of the way there, but that last parameter to substr is obviously wrong.
You need to use another instr call to get the position of the 4th . and then substract the position of the 3rd . to get the number of characters to substr.

Report message to a moderator

Re: extract character between points [message #632833 is a reply to message #632831]

Fri, 06 February 2015 03:02

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

So you need 3 instr calls in total, or 2 if you do some nesting.
Try and make it work with 3 first.

Report message to a moderator

Re: extract character between points [message #632835 is a reply to message #632831]

Fri, 06 February 2015 03:05

mmm286
Messages: 14
Registered: July 2014

Junior Member

cookiemonster wrote on Fri, 06 February 2015 02:58

You're part of the way there, but that last parameter to substr is obviously wrong.
You need to use another instr call to get the position of the 4th . and then substract the position of the 3rd . to get the number of characters to substr.

Thanks but doesn't works

select (substr(xxxx,instr(xxxx, '.',1,3)+1,instr(xxxx,'.',1,4))) xxxx from yyyy ;

Report message to a moderator

Re: extract character between points [message #632837 is a reply to message #632835]

Fri, 06 February 2015 03:15

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

mmm286 wrote on Fri, 06 February 2015 09:05

cookiemonster wrote on Fri, 06 February 2015 02:58
You're part of the way there, but that last parameter to substr is obviously wrong.
You need to use another instr call to get the position of the 4th . and then substract the position of the 3rd . to get the number of characters to substr.

Report message to a moderator

Re: extract character between points [message #632850 is a reply to message #632837]

Fri, 06 February 2015 04:24

mmm286
Messages: 14
Registered: July 2014

Junior Member

cookiemonster wrote on Fri, 06 February 2015 03:15

mmm286 wrote on Fri, 06 February 2015 09:05
cookiemonster wrote on Fri, 06 February 2015 02:58
You're part of the way there, but that last parameter to substr is obviously wrong.
You need to use another instr call to get the position of the 4th . and then substract the position of the 3rd . to get the number of characters to substr.

Take me a hand please?

Report message to a moderator

Re: extract character between points [message #632852 is a reply to message #632850]

Fri, 06 February 2015 04:33

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

make.23.6789.323424.000.ksh
            ^      ^
            |      | position of the 4th dot = 20
            | position of the 3rd dot = 13

select substr(your_string, 13 + 1, (20 - 13) - 1) from your_table

[Updated on: Fri, 06 February 2015 04:34]

Report message to a moderator

Previous Topic:	query in CASE expression in select
Next Topic:	Insert data through Java class

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Aug 27 01:33:47 CDT 2026