The Little Guy Posted March 4, 2011 Share Posted March 4, 2011 in a match against search, is it possible to ignore punctuation? for example, I have McDonald's in the table, and if I do a search like this mcdonalds, I get no results. If I search for mcdonald I will get results. So how can I ignore punctuation marks, so no matter how I search mcdonald, mcdonalds, McDonald's I will get similar results? Quote Link to comment Share on other sites More sharing options...
cunoodle2 Posted March 4, 2011 Share Posted March 4, 2011 Can you use a regular expression in php to first remove any punctuation?? $string = preg_replace('/[^a-zA-Z0-9 ]/','',$string); Quote Link to comment Share on other sites More sharing options...
The Little Guy Posted March 4, 2011 Author Share Posted March 4, 2011 no, I need to keep the format of the text in the database Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted March 4, 2011 Share Posted March 4, 2011 You can't do that. The punctuation isn't the problem, the problem is that you are searching for either '%value%' or 'value%'. either way, your search string doesn't match the value in the table. It's nothing to do with punctuation. e.g. if you had 'Taco Bell' in your database and searched for 'Taco Belle' you would get nothing, nor would you get anything if you searched for ' Tacco Bell' or 'Taco Sell'. This is because the characters you are submiting in the string do not match the characters that are in the same postion in the strings that are already stored. Quote Link to comment Share on other sites More sharing options...
The Little Guy Posted March 4, 2011 Author Share Posted March 4, 2011 I have "Taco Bell" in the database, and searching for "Taco Belle", "Taco Sell", and "Tacco Bell", all of them return a result. You must have not read the first line, I am doing Full Text searches using a "Match Against" Quote Link to comment Share on other sites More sharing options...
TOA Posted March 5, 2011 Share Posted March 5, 2011 Can you use a regular expression in php to first remove any punctuation?? $string = preg_replace('/[^a-zA-Z0-9 ]/','',$string); no, I need to keep the format of the text in the database I'm curious, how would it affect the format in the database? Wouldn't it just switch mcdonald's to mcdonalds in the string to use in the match? Quote Link to comment Share on other sites More sharing options...
The Little Guy Posted March 5, 2011 Author Share Posted March 5, 2011 Your right, but it won't work in my case, because the string in the database will still say mcdonald's Quote Link to comment Share on other sites More sharing options...
ignace Posted March 5, 2011 Share Posted March 5, 2011 What's the definition of the fields you are searching upon? Are they VARBINARY? Or do they have a *_bin collation? Quote Link to comment Share on other sites More sharing options...
fenway Posted March 5, 2011 Share Posted March 5, 2011 None of this matters. You need to store a clean version of your strings, depending on how you're searching. Substring matches depend on the content, same with the definition of "alphanumeric', the list goes on. Without true full-text searching (i.e. stemming, etc.), this is all smoke and mirrors. Quote Link to comment Share on other sites More sharing options...
The Little Guy Posted March 5, 2011 Author Share Posted March 5, 2011 Alright, I think I did what someone suggested, I made two column, one is the display text column and one is a search column. I then do a match on both columns like so: MATCH (C.name, C.searchName) AGAINST('some search string' IN BOOLEAN MODE) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.