Re: SUBSTR & INSTR

stephen O'D (stephen.odonnell_at_gmail.com) wrote:
: On Jun 16, 5:48 pm, Mtek <m..._at_mtekusa.com> wrote:
: > Hi,
: >
: > I'm looking to parse an email. Say I have an email like this:
: >
: > johnjo..._at_gmail.abc.def.ghi.com
: >
: > I basically want johnjo..._at_gmail.abc.def.ghi in one variable and
: > the .com in the other.
: >
: > If the email is this:
: >
: > jjo..._at_amail.co
: >
: > The same, I basically want jjo..._at_amail.co in one variable and
: > the .com in the other.
: >
: > I know I have to use SUBSTR & INSTR, but the problem is that I do not
: > know how many periods may be in the email address.....
: >
: > Any takers for some help??
: >
: > Thanks!
: >
: > John

: Depending on whether you are on Oracle 10G or not, regexp_instr and
: regexp_substr may be a great help here -
: http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/rischert_regexp_pt1.html

INSTR( string_to_search , string_to_find , start_position , occurrence )

start_position can be negative, and then the search is backwards from the end. That allows you to find the last occurrence of a sub string.

select instr( '12345678901234567890' , '8' , -1 ) from dual ;

INSTR('12345678901234567890','8',-1)

                                  18

$0.10 Received on Wed Jun 18 2008 - 18:29:46 CDT