SUBSTR & INSTR
in [Oracle Help]
|
Prev: ORA-00911 error and COMMIT
Next: Los Angeles Locksmith Services, Lockout Rekey Install Locks and Keys Made Home Car Auto
From: Mtek on 16 Jun 2008 12:48 Hi, I'm looking to parse an email. Say I have an email like this: johnjones(a)gmail.abc.def.ghi.com I basically want johnjones(a)gmail.abc.def.ghi in one variable and the .com in the other. If the email is this: jjones(a)amail.co The same, I basically want jjones(a)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
From: joel garry on 16 Jun 2008 13:01 On Jun 16, 9:48 am, Mtek <m...(a)mtekusa.com> wrote: > Hi, > > I'm looking to parse an email. Say I have an email like this: > > johnjo...(a)gmail.abc.def.ghi.com > > I basically want johnjo...(a)gmail.abc.def.ghi in one variable and > the .com in the other. > > If the email is this: > > jjo...(a)amail.co > > The same, I basically want jjo...(a)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 Can you not just check whatever is after the last period and decide then? There are only so many domains in the world. See the docs on instr about how to search backwards from the end of the string. But I must say, one of my domains ends with .to and does not have a .com, so I guess I'm thankful you can't spam me! :-) jg -- @home.com is bogus. Sorry, not drunk enough: http://www.signonsandiego.com/uniontrib/20080614/news_1b14lerach.html
From: Mtek on 16 Jun 2008 13:25 On Jun 16, 12:01 pm, joel garry <joel-ga...(a)home.com> wrote: > On Jun 16, 9:48 am, Mtek <m...(a)mtekusa.com> wrote: > > > > > Hi, > > > I'm looking to parse an email. Say I have an email like this: > > > johnjo...(a)gmail.abc.def.ghi.com > > > I basically want johnjo...(a)gmail.abc.def.ghi in one variable and > > the .com in the other. > > > If the email is this: > > > jjo...(a)amail.co > > > The same, I basically want jjo...(a)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 > > Can you not just check whatever is after the last period and decide > then? There are only so many domains in the world. See the docs on > instr about how to search backwards from the end of the string. > > But I must say, one of my domains ends with .to and does not have > a .com, so I guess I'm thankful you can't spam me! :-) > > jg > -- > @home.com is bogus. > Sorry, not drunk enough: http://www.signonsandiego.com/uniontrib/20080614/news_1b14lerach.html Thanks a bunch. Was unaware that Oracle offered a reverse INSTR lookup.....
From: sybrandb on 16 Jun 2008 16:35 On Mon, 16 Jun 2008 10:25:13 -0700 (PDT), Mtek <mtek(a)mtekusa.com> wrote: >Thanks a bunch. Was unaware that Oracle offered a reverse INSTR >lookup..... Not surprisingly. Most questions here and on OTN are redundant, while doc questions. Most people don't read docs before posting, and continue not to read them after being pointed to the docs. -- Sybrand Bakker Senior Oracle DBA
From: stephen O'D on 18 Jun 2008 18:08
On Jun 16, 5:48 pm, Mtek <m...(a)mtekusa.com> wrote: > Hi, > > I'm looking to parse an email. Say I have an email like this: > > johnjo...(a)gmail.abc.def.ghi.com > > I basically want johnjo...(a)gmail.abc.def.ghi in one variable and > the .com in the other. > > If the email is this: > > jjo...(a)amail.co > > The same, I basically want jjo...(a)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 |