-1

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

Hasan Ali
  • 252
  • 4
  • 13
bean2k1
  • 1
  • 1

4 Answers4

0

You can use pattern matching to solve this issue.

What you need to know is the pattern you are searching for, to give you an example if you are searching for all postcodes starting with B only then you know that these postcodes will

  1. Must have first character as B. There are examples in link to select first character (^) and 1 instance({1} equal to 'B' or 'b'.
  2. Second character can be any number

Similarly you can do for all other patterns as well.

Farrukh Subhani
  • 2,018
  • 1
  • 17
  • 25
0

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
Franck
  • 2,459
  • 3
  • 16
  • 22
0

You can use REGEXP clause for the same.

E.g.: select * from orders where (InvoicePostcode REGEXP '^B[^BS].' OR InvoicePostcode REGEXP '^WS.')

'^B[^BS].*' in this it will ignore words start with BB OR BS.

Hope it'll help you out.

Pradeep Rajput
  • 724
  • 7
  • 11
0
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

pls refer : https://www.guru99.com/regular-expressions.html

RLIKE operator performs a pattern match of a string expression against a pattern. The pattern is supplied as an argument. The following MySQL statement will find the author’s name beginning with ‘B’. The ‘^’ have been used to match the beginning of the name.

SELECT * FROM orders WHERE InvoicePostcode RLIKE '^B';

[A-Z] Matches any uppercase letter​

[a-z] Matches any lowercase letter​

'^(B[A-Z])'means it will work as first letter B and whether next is A to Z are not.

Ponni
  • 433
  • 5
  • 17