FW: Soundex in Informix, how?

We implemented Soundex using a sister table for the one for which we needed the search, but this could just as easily be done within the same table. First, you need to decide upon your Soundex algorithm. I wrote mine as a customized extension to the Russellian Soundex. One of my extensions, for example, turn the letter '&' into the word "AND" so that "TOM & HUCK" and "TOM AND HUCK" look exactly alike. Implement your tables like this: customer and cust_soundex or just customer ----------------- ------------------- ----------------- customer_id customer_id customer_id last_name last_name last_name first_name first_name first_name last_first last_soundex first_last first_soundex last_first_sndx first_last_sndx We use two tables. Implement a trigger-based audit trail on the customer table for all inserts, updates, and deletes. Don't allow anyone to put data into the soundex field names if you use the same-table method. Then write a 4GL that reads the audit-trail and performs the synchronization of the soundex data with the regular data. The 4GL program should be kicked off by cron. We do it once every hour. Then you'll need to write your interface so that it soundexes your search string prior to the select. For example: function get_customer() define cust_name char(30) define cust_rec record like customer.* prompt "Enter Customer Name: " for cust_name let cust_name = Soundex(cust_name) declare cust_curs cursor for select customer.* from customer c, cust_soundex cs where c.customer_id = cs.customer_id and (cs.last_name like cust_name or cs.first_name like cust_name or cs.last_first like cust_name or cs.first_last like cust_name) # rest of code here end function I would recommend that your Soundex function be able to set a granularity. For example, if your Soundex alogrithm processes SMITH into SMT, and processes SMITHSONIAN into SMTSNN, a search by full soundex would not return the two rows. But, searching on a granularity of 3 would. Therefore, your Soundex call above would look like: let cust_name = Soundex(cust_name, 3) If cust_name = "SMITH" or "SMITHSONIAN" or "SMYTHE" or "SMITTY", then you'd get back "SMT%". You could also implement for MATHCES to return "SMT*" We attempted to get this into a stored procedure, but SP won't allow us to perform string-parsing. I have a Soundex implementation in 4GL and C. If you'd like for me to e-mail one to you, write me back. Joe Morgan Hastings Books, Music & Video morganj@hasting.com >>I would like to use a soundex like function where I want to a get a set >>of rows that based on a string (column) which is similar or close to a >>desired string. >> >>Something like: >> >>select name from terms from name = "FAQ">> >>I would to get back names like "FAQ", "PHAQ", "FAK", "PHAK", "FAC", etc... >> >>Abdu