Re: Case-insensitive search solution wanted

In article <CzMz5C.JK8@tyrell.net>, wonus@tyrell.net (Kevin Wonus) writes: >It is quite sloppy, but searching for John Doe, you can use the non- >standard matches operator: >WHERE NameFull matches "[Jj][Oo][Hh][Nn] [Dd][Oo][Ee]" >The problem is performance. If you need rapid response (i.e. you >have a very large database), you will need a redundant field which >contains an all lower case version of the string. (or perhaps >an additional field which has a capitalization rule... you have >to beware of last names such as O'Reily or Van Elswyk) I think there should be an optional clause on the CONSTRUCT statement that builds the above where clause string rather than having to do it manually all the time. BTW, there is a company called Search Software America that has a library of C routines especially built for name searching and matching. Basically, you have to add an extra integer field in your table, and then you pass the name into their routine. It returns a 16-byte "key", which you store with each name. When you want to search, you pass the search criteria into their routine. Their routine returns two integers ("keys") that can be used in a SQL BETWEEN clause. The cool thing about this routine is that it finds names that are "close", even those names that "sound" like the search criteria. Another nice feature is that each that match is "scored" and you can set the sensitivity of the search numerically. Let me know and I'll dig up their address/phone and email it to you. Regards, Mike Teachworth CodeGen Software, Inc 504-456-0123 voice 504-456-1260 fax mike@codegen.com