-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

I realized this also. Martin Schwarz off list suggested to me to use
REGEXP for that purpose. That means that I'm rewriting the user input
"übersee" behind the scenes to "^(ü|ue)bersee.*" which works quote well.

The only gotcha is that the user may enter any special regexp characters
also, so I had to make a list of them and escape them prior to modifying
the string.

thanks,
- - Markus

sheeri kritzer wrote:
> MySQL doesn't have anything like that.  You can use the wildcard
> characters instead of the umlauts if you want, such as
> 
> SELECT * from person where name like "%bersee"
> which would get
> "übersee" and "uebersee"
> but also a whole lot more.
> 
> But doing something like
> SELECT * from person where name like "_bersee" or name like "__bersee"
> might work -- the underscore means "1 of any character", so here the
> only noise you'd get are other folks whose names are
> _  _  bersee
> So there's still a margin for error.
> 
> Unfortunately, there's no special case for "hey, when you're looking
> at LIKE, I want to define that x=y" -- particularly when x and y have
> differing #'s of characters.
> 
> -Sheeri
> On 3/22/06, Markus Fischer <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> what is the best way to match german umlauts like 'ä' also their
> alternative writing 'ae'?
> 
> For example I'm searching for "übersee" and I also want to find the word
> "uebersee" in the database. The "words" are actually names of persons.
> 
> One possibility  is to dynamically expand the SQL statement if such
> special characters are found. So the search term "übersee" will be
> expanded to "SELECT * FROM person WHERE name LIKE 'übersee%' AND name
> LIKE 'uebersee%'" but this is getting dirty and very very long if
> multiple umlauts are used to cover all cases ...
> 
> So the other idea is to have the name twice in the database for every
> person and the second "version" of the name is a normalized for where
> all special characters are replaced with their alternative writing. E.g.
> I store the field name "übersee" and also name2 "uebersee" and when
> matching I match against name2. If the field would container more
> special characters it still would work without much more work, e.g. name
> is "überseemöbel" then name2 would be "ueberseemoebel" and when the term
> "überseemö" is entered it's also normalized to "ueberseemoe" and the
> LIKE statement will still match. Basically this is some kind of
> primitive stemming like lucene does it.
> 
> Is there maybe some built-in support from MySQL for such special cases?
> 
> thanks for any pointers,
> - Markus
>>
- --
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]
>>
>>

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFEI4BC1nS0RcInK9ARAnMrAJ9jZ5LAxS1S2bjqrPvIUBSiTGsxxQCgv+5l
xyxQhd7B9HTnc8sTa7Tsekk=
=uycm
-----END PGP SIGNATURE-----

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to