Mysql: How to strip out all the but letters and numbers in a column before performing a LIKE with a given string -

i wish know how google search, there road called st. john's road in database , type in ajax-auto-sugestion field...

st john

how can auto-selection show road content not use . or ' in database

if start type...

st. john

then of course show suggestion used .

can done ?

my fields have proper names of roads , use utf8_general_ci road name field , method results.

what using on server users interface mysql server? typically people use php process information between database , html on user's side.

to purely in mysql (which i'm not sure you're trying accomplish), can use update table set field_name = replace( replace(field_name, '.', ''), '"'. ' ' );

this replace period no white space same, , periods whitespace space.

it's easier in opinion sort of stuff in php, can use str_replace.

this way you'll end using

$address = ($_post['address']); $address = str_replace('.', '', $address);

this can done before writing like statement in mysql. can read more like statement here: mysql operator

where $_post['address'] returned <input> element user typed address in, , submitted through form. doing , autocomplete / autoselect in ajax bit more difficult, it's not trivial send ajax requests on typing actions. surely else can explain that, time have ever done ajax based autosuggest, i've preloaded array mysql through php. i'm sure google doesn't that.