Re: Searching on AlphaNumeric Content Only

On Wed, 2009-09-02 at 21:30 -0700, Ben Dunlap wrote: > > Is there is a way to search only for the alphanumeric content of > > field in a db? I have an itemID field that contains item #'s that include > > dashes, forward slashes, etc, and I want people to be able to search for an > > item # even if they don't enter the punctuation exactly. > > Not sure if there's anything specifically PHP-ish that will help you > here, but I would be inclined to start by storing a stripped-down > version of the item ID (alphanumeric characters only) in a separate > column in the database table. > > Then, when a user enters some search data, I would remove > non-alphanumeric characters, if any, from the user's input, and then > search the stripped column with this normalized version of the input. > > If you want even fuzzier matching (inadvertent transpositions or an > omitted character or two OK, for example), you might read about > Levenshtein distance: > > http://en.wikipedia.org/wiki/Levenshtein_distance > > PHP has a levenshtein function but you'll have to figure out a way to > use it efficiently with your data set. Or, if Levenshtein isn't quite > right for your needs, the article above might at least point you in a > useful direction. > > Ben > What's wrong with using the wildcards that are built into most SQL variants? SELECT * FROM table WHERE item_id LIKE '%#abcdef' Will select all records where the item_id field ends in '#abcdef' Thanks, Ash http://www.ashleysheridan.co.uk