0

How can I go about querying a MySQL column that contains white spaces within the value when my query sometimes does not contain a white space?

My example is:

Query: select * where postcode = 'SW1A2AA'

Data structure:

id    |   postcode   |  address
1         SW1A 2AA      10 downing street, london, SW1A 2AA

I am using laravel as the backend and i have tried to use %like% and also %%like%% using a raw query but for some reason nothing seems to work.

my basic query is: 

   public function lookup($postcode){
     $db = postcodeLookup::where('postcode', $postcode)->get();
     return $db;
   }

This works fine as long as the postcode query is the same as the value stored in the DB, So if my query contains spaces it simply returns null.

Any help is greatly appreciated, Thanks.

Birdy
  • 775
  • 1
  • 6
  • 21
  • rule of thumb is to format data upon inputting into DB. So when inserting the postcode remove all whitespaces first. – Andrew Jan 24 '17 at 10:01
  • fyi http://stackoverflow.com/a/10654404/3273588 – Andrew Jan 24 '17 at 10:03
  • @Andrew - To be fair i have done that on dev server and run a MySQL replace query to remove all white spaces within the postcode column, The only problem is that means i need to either restrict the input field to not allow any spaces or strip the string prior to the query, I thought there may have been or more elegant way. – Birdy Jan 24 '17 at 10:04
  • 1
    @Birdy No way round that - users enter bad data and one needs to clean it up before storing. Otherwise you end up with slow, complex queries trying handle the bad data. :-) All the best – Andrew Jan 24 '17 at 12:44

0 Answers0