Search code examples
phpmysqlsearchpostal-code

Mysql Search Postcodes

I want to create a mysql search query, that will search for postcodes here is what i have done so far

SELECT * FROM orders where ( InvoicePostcode LIKE 'b%' OR InvoicePostcode LIKE 'ws%') order by InvoiceNumber asc

I want to display only postcode starting with b or ws but the problem is some post contain bb or BS at the start of the postcode for example

BS24 8EE

BB9 8SY

the only ones that should be showing that start with b like this one B65 0NQ im not sure how to check postcode by only looking at the letter at the start before the numbers in the postcode

Solution

  • You can try to use regexp to exclude certain pattern:

    In the below I exclude InvoicePostcode containing B followed by a letter

    SELECT * FROM orders
WHERE (InvoicePostcode LIKE 'b%' AND NOT InvoicePostcode RLIKE '^(B[A-Z])')
    OR (InvoicePostcode LIKE 'ws%' AND NOT InvoicePostcode RLIKE '^(WS[A-Z])')
ORDER BY InvoiceNumber ASC