Hi,

we are using this construct
replace(replace(replace(replace(replace(replace(re place(replace(replace(replace(
replace(replace(replace(
lower(ANY_NAME),
' ',''),',',''),'ä','a'),'ü','u'),'ö','o'),'ß','s'), 'oe','o'
),'ue','u' ),'ae','a'),
'y','i'),'ll','l'),'nn','n'),'ss','s')

for comparing a given person-name with our database-person-names

is there a better solution or are the multiple replaces fast enough.
In my programms I iterate char by char and substitute using fast
case-constructs for substitution.
But I don't know how fast a char-by-char-solution in a PL/SQL-function
would be.

--
Norbert
Oracle9i Enterprise Edition Release 9.2.0.8.0 64Bit

On 9 mar, 15:09, Norbert Winkler <norbert.winkl... (AT) gmx (DOT) de> wrote:
TRANSLATE() ?

HTH

Cheers.

Carlos.

On 9 mrt, 15:09, Norbert Winkler <norbert.winkl... (AT) gmx (DOT) de> wrote:
UTL_MATCH?

hth,
Frans H.,

On Mar 9, 10:52*am, fra... (AT) hotmail (DOT) com wrote:

Have you checked into the pl/sql soundex function? According to the
pages that came up in google, this might be worth trying if the names
being compared are likely to be phonetically similar.

Am Mon, 9 Mar 2009 07:52:48 -0700 (PDT) schrieb fransh (AT) hotmail (DOT) com:

Thanks for advising to this, the "Levenshtein Distance" is IMHO the best
solution for similarity, I did'nt know that it exists in Oracle.

And in
http://www.oracle.com/technology/boo...l%20ch%201.pdf
I see it's the well known algorithm they iterate in PL/SQL.
So I could give it a try to compare the speed of replace(replace(..
by a PL/SQL-cylce.

btw.
TRANSLATE() isn't a solution because I want to change German Umlaut Ä -->
AE (1 char into 2 chars)


--
Norbert
Oracle9i Enterprise Edition Release 9.2.0.8.0 64Bit

Norbert Winkler wrote:

Norbert, I see in your sig:
"Oracle9i Enterprise Edition Release 9.2.0.8.0 64Bit"

isn't it about time to upgrade. Now that Oracle is no longer back
porting security patches to this version, it should be considered for
your critical systems.

Am Thu, 12 Mar 2009 20:28:29 -0500 schrieb Michael Austin:

It's right - I'd prefer a version with implemented regular expression or
the Levensthein-algorith. But I'm only a user.
And the others like "never touch a running system" ....



--
Norbert
Oracle9i Enterprise Edition Release 9.2.0.8.0 64Bit

On Mar 17, 3:05 pm, Norbert Winkler <norbert.winkl... (AT) gmx (DOT) de> wrote:



"Why bother abolishing slavery - it's worked for thousands of years?"

;-)



Well, Norbert, do you have the source code to the application
that you're running?

If you do, why not put in a shadow column "search_name" ,
capitalise everything in it, and when users put in
an umlaut, convert it to AE before inserting?

Then, when searching, if the user inputs an umlaut, change
the search string accordingly and search against search_name.

I did this once with Irish names -

NAME SEARCH_NAME
McGuire MACGUIRE
MCGuire MACGUIRE
MacGuire MACGUIRE
MACGuire MACGUIRE
MaCGuire MACGUIRE
MaGuire MACGUIRE


Helps eliminate different/mistaken spellings.

Help?


Paul...