CanMan2004 Posted September 10, 2006 Share Posted September 10, 2006 Hi allI am trying to perform a query on a field on my sql database called pcode, basically the field holds the data for a postcode, so for example, the rows might look likeW1H 7YHST64 2HGN12 2ESI want to perform a query on the postcode field allowing rows to be found which contain any kind of match, so it ignores spaces placed in the search box or in the field.For example, if someone does a search on "W1H7HG" then it would return the rows that start or contain the whole postcode "W1H 7HG",again, if they did a search for "S E2 2 7Y H" then it would return the rows that start or contain the whole postcode "SE22 7YH",and again if they did a search for "E H 4" then it would return the rows that start or contain the whole postcode "EH4" in the fieldI hope that makes some sense, im basically trying to get around people putting extra spaces into the search or missing a space, I normally use the code[code]pcode LIKE '%".$_GET['pcode']."%'[/code]But it seems to NOT ignore additional spaces.Is there a wildcard search to do what I want?Thanks in advanceEd Quote Link to comment Share on other sites More sharing options...
Barand Posted September 10, 2006 Share Posted September 10, 2006 Because the British establishment seems incapable of coming up with standard structures for code (be it phone area codes or postcodes) I store postcode fields twice in my postcode table, formatted and with no spaces for searching[pre]| W1H 7YH | W1H7YH || ST64 2HG | ST642HG || N12 2ES | N122ES |[/pre] Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 10, 2006 Author Share Posted September 10, 2006 no easy way around it then? Quote Link to comment Share on other sites More sharing options...
Daniel0 Posted September 10, 2006 Share Posted September 10, 2006 [quote author=Barand link=topic=107539.msg431612#msg431612 date=1157897351]Because the British establishment seems incapable of coming up with standard structures for code (be it phone area codes or postcodes) I store postcode fields twice in my postcode table, formatted and with no spaces for searching[pre]| W1H 7YH | W1H7YH || ST64 2HG | ST642HG || N12 2ES | N122ES |[/pre][/quote]Wouldn't it be easier to do [code]str_replace(' ','',$string)[/code] on them? Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 10, 2006 Author Share Posted September 10, 2006 It would be, but the postcodes in the sql database have spaces in them, unless you can put[code]str_replace(' ','',$string)[/code]into the query, so it views the data in the sql database without spaces and does a search, is that possible? I imagine it's not as simple as[code]$temp = str_replace(" ", "", postcode);$sql = "SELECT * FROM users WHERE $temp LIKE '%".$_GET['postcode']."%'";[/code]Then I could remove the spaces of the forms posted field and get a matchCan this be done?Thanks in advanceEd Quote Link to comment Share on other sites More sharing options...
Barand Posted September 10, 2006 Share Posted September 10, 2006 I'm useless at regex, but if there's a pattern to strip out spaces from a string you could have something like this (not sure of exact syntax for regex() ).... WHERE REGEX(pcode, pattern) LIKE '%$search%' Quote Link to comment Share on other sites More sharing options...
Barand Posted September 11, 2006 Share Posted September 11, 2006 Just checked the MySQL manual - there is a str_replace equivalent... WHERE REPLACE(pcode, ' ', '') LIKE '%$search%' 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.