MySQL query - select postcode matches

Question

Hello

I need to make a selection based on the first 2 characters of a field, so for example

SELECT * from table WHERE postcode LIKE 'rh%'

But this would select any record that contains those 2 characters at any point in the "postcode" field right? I am in need of a query that just selects the first 2 characters. Any pointerS?

Thanks

Answer 1

Your query is correct. It searches for postcodes starting with "rh".

In contrast, if you wanted to search for postcodes containing the string "rh" anywhere in the field, you would write:

SELECT * from table WHERE postcode LIKE '%rh%'

Edit:

To answer your comment, you can use either or both % and _ for relatively simple searches. As you have noticed already, % matches any number of characters whereas _ matches a single character.

So, in order to match postcodes starting with "RHx " (where x is any character) your query would be:

SELECT * from table WHERE postcode LIKE 'RH_ %'

(mind the space after _). For more complex search patterns, you need to read about regular expressions.

Further reading:

http://dev.mysql.com/doc/refman/5.1/en/pattern-matching.html

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Answer 2

LIKE '%rh%' will return all rows with 'rh' anywhere

LIKE 'rh%' will return all rows with 'rh' at the beginning

LIKE '%rh' will return all rows with 'rh' at the end.

If you want to get only first two characters 'rh', use MySQL SUBSTR() function http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_substr

Answer 3

Dave, your way seems correct to me (and works on my test data). Using a leading % as well will match anywhere in the string which obviously isn't desirable when dealing with postcodes.