Re: parsing using sql help please

From: Harel Safra <harel.safra_at_gmail.com>
Date: Tue, 10 Jan 2012 21:04:41 +0200
Message-ID: <CA+UC=5HpG8Kbii2fkD-o_ZUzN-SReHJUsr9FCfqxD+Ut_zGuNg_at_mail.gmail.com>

"Can instr() be used to search in reverse?" yes:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions068.htm " If position is negative, then Oracle counts backward from the end of string and then searches backward from the resulting position." Harel

On Tue, Jan 10, 2012 at 8:58 PM, Eugene Pipko <eugene.pipko_at_unionbay.com>wrote:

> 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

Received on Tue Jan 10 2012 - 13:04:41 CST