RE: parsing using sql help please

-----Original Message-----
From: Kamran Agayev (ICT/SNO) [mailto:itakamran_at_azercell.com] Sent: Tuesday, January 10, 2012 11:07 AM To: Eugene Pipko; oracle-l_at_freelists.org Subject: RE: parsing using sql help please

Do you mean this:

SQL> select instr('Eugene A Pipko',' ',-1) from dual   2 ;

INSTR('EUGENEAPIPKO','',-1)

                          9

SQL> Kamran Agayev A.
Oracle ACE, OCP
http://kamranagayev.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Eugene Pipko Sent: Tuesday, 10 January 2012 10:59 PM
To: oracle-l_at_freelists.org
Subject: parsing using sql help please

Hi all,
I need to be able to parse list of customers by first/last names. The problems is that each name can come in many different ways. For instance:
'Eugene Pipko'
'Eugene Pipko' -- many blanks in between
'Eugene A Pipko' -- mid initial with one space in between
'Eugene A. Pipko' -- mid initial with a dot and multiple spaces

I am thinking of getting first name using substr('Eugene Pipko',1,instrt('Eugene Pipko',' ',1,1)) to start searching from the beginning of the name. Now, the last name can be found by looking backwards till the first blank, but ... I don't know how to do it. I can user substr() to search backwards, but I need to find first blank starting from the end. Can instr() be used to search in reverse?

[cid:image001.png_at_01CCCF86.CDDE0A70]

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l