Soundex type search (fwd)
Luis Caamano writes: > > Hello, everybody. > > Does anybody knows of any way to implement sound matching searches > on character fields? That is, to match words that have the same > sound not the spelling? > > It don't care how as long as I'm reading from an informix database. > > I think that the only way would be with c-isam, since SQL doesn't > have that capability. The way I see it is to have a normal 4gl app > handling the data entry and another module developed in C with > C-isam doing the soundex search. > > Any comments? > > Any help would be greatly appreciated. > > -- > --------------------------------------------------------------------------- > Luis P. Caamano | > Support Services Engineer | uunet!ddssuprs!lpc > Dickens Data Systems, Norcross GA | (404) 242-5991 > --------------------------------------------------------------------------- > If you think you know something, you'll stop learning. -myself > Luis - Soundex involves conversion of the character string in question into another string based on the soundex algorithm - that string can then be used for comparisons as required. As such, putting soundex into a database is really pretty straightforward: eg. Let's say you have a table CUSTOMER, and you want to provide soundex search on the column LAST_NAME: 1. Create an additional char(4) column to contain the soundex code (eg. LAST_NAME_SOUNDEX) and maintain an index on this column. 2. Each time a LAST_NAME is INSERTed or UPDATEd, compute and update the corresponding LAST_NAME_SOUNDEX value. 3. To support queries, allow the user to enter the "sounds like" name, convert it to SOUNDEX and query the table by LAST_NAME_SOUNDEX. The soundex algorithm is very simple - it is explained in "The Art of Computer Programming - VOL 3. Sorting & Searching", D. E. Knuth, on page 392. Examples given in the above reference of names and their equivalent soundex codes: NAME SOUNDEX CODE Euler E460 Ellery E460 Gauss G200 Ghosh G200 Hilbert H416 Heilbronn H416 Knuth K530 Kant K530 Lloyd L300 Ladd L300 Lukasiewicz L222 Lissajous L222 ------------ DHL WORLDWIDE EXPRESS ------------------------------------------- Greg Bryan gbryan@ssf-sys.dhl.com DHL Systems Data Administrator uunet!ssf-sys.dhl.com!gbryan San Francisco -------------------------------------------------------------------------------