Michael Maddison
michael at ddisolutions.com.au
Wed Jun 9 07:48:30 CDT 2010
Actually pre-processing and plonking the result in another field would work very well for large tables. This sucker only has 5K names after 6-7 years so I don't think performance will be much of an issue. Cheers Michael M I'm thinking that you should preprocess to get the name standardized. Assuming that O Tool is not a valid name, process the name (in the table) such that O Tool is replaced with O'Tool and OTool is replaced with O'Tool and then teach the user that there are ' in names. OTOH... As I cruise email and the internet I constantly see people replace lose with loose (I will loose my sanity) so I am not convinced that there is a solution for your problem. There is no substitute for the ability to think. I actually had a name substitution library that I contributed to which does what you are talking about. It was a rather extensive collection of the hyphenated names culled from huge name lists. And of course I cannot find it anymore. Sigh. The concept is that you build a substitution table and then just search the substitution table. I have searched and searched and simply cannot find it now. John W. Colby www.ColbyConsulting.com Michael Maddison wrote: > Ugly, yes. > It will be war on hyphens next. > > I guess this will work. > WHERE Surname like '%' + @search + '%' OR --The can spell way > replace( Surname,'''','') like '%' + replace(@search,' ','') + > '%' > > Michael M > > > The obvious would be to do a replace on ' and space and then compare > against names pre-stripped. > > Ugly in any case. > > John W. Colby > www.ColbyConsulting.com > > > Michael Maddison wrote: >> Hi Guys, >> >> >> >> It's late at night and I really don't know how to respond to this >> request... >> >> >> >> "Currently for a name such as O'Tool you have to enter in the ' to get >> the name to come up, is it possible to get the search to recognise a >> name such as this when options such as O Tool or Otool are put in by > the >> user" >> >> >> >> My 1st reaction was to laugh, should I be crying? >> >> I think I'd have to do a replace on the field side of the where >> clause... I'd have to do a replace for each scenario I think. >> >> >> >> Ideas? >> >> >> >> Cheers >> >> >> >> Michael M _______________________________________________ dba-VB mailing list dba-VB at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-vb http://www.databaseadvisors.com No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.829 / Virus Database: 271.1.1/2926 - Release Date: 06/09/10 04:35:00