Msg#: 4405594 posted 5:52 pm on Jan 10, 2012 (gmt 0)
Ooops, found that that doesn't work. I've now refined it down to the following:
SELECT field1, field2, REPLACE(field3,'\','') as NewName FROM mytable HAVING NewName LIKE 'archers place'
This seems to work fine but my original question still applies - is this the correct/best way of doing it.
I have hit one further problem. Some names include an & in the table, but are excluded in the friendly-URLs. So, the database might contain "Town & Country Supplies" and my source data which I search on will be "town country supplies".
Have I bitten off more than I can chew with this one? I can see no way of my php script 'guessing' where apostrophes and ampersands might be needed, so it is very much a matter of the query having to manipulate the table. Having spent the afternoon on this, I think I need something stronger than coffee!
And if that is too cumbersome you may consider your own site search server and indexing process. I prefer using an indexer and my own site search engine. Sphinx Search [sphinxsearch.com] is a whole new animal but well worth the effort once you get it nailed down. It indexes your data, in this case your MySQL data, and once the search terms are in the index you can use Sphinx to do the grunt work.