stephen O'D (stephen.odonnell (AT) gmail (DOT) com) wrote:
: On Jun 16, 5:48 pm, Mtek <m... (AT) mtekusa (DOT) com> wrote:
: > Hi,
: >
: > I'm looking to parse an email. Say I have an email like this:
: >
: > johnjo... (AT) gmail (DOT) abc.def.ghi.com
: >
: > I basically want johnjo... (AT) gmail (DOT) abc.def.ghi in one variable and
: > the .com in the other.
: >
: > If the email is this:
: >
: > jjo... (AT) amail (DOT) co
: >
: > The same, I basically want jjo... (AT) amail (DOT) 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/ora...egexp_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