Matching in MySQL
Archive - Originally posted on "The Horse's Mouth" - 2005-09-24 09:54:57 - Graham Ellis"I want to find all records that look like ...". Such is a common sayingwhen searching a database table (or a text file or other data source come to that), but the words "look like" are very woolly and can mean different things to different people. So when you're writing code to find matching records, you've got a choice of techniques available to you.
Let's say, for example, that you want to match a British postcode that's contained in a field called postcode in a table. Here are three options.
First, an exact match:
SELECT * FROM contacts WHERE postcode = "SN12 6QL"
will match just the one postcode.
Second, using the LIKE operator will let you specify a simple pattern to match using _ to represent any one character and % to represent any string. So:
SELECT * FROM contacts WHERE postcode LIKE "SN12 %"
will match any postcode that starts with SN12
Finally, if you use the RLIKE or REGEXP operator you can specify a regular expression that you want to match. For example:
SELECT * FROM contacts WHERE postcode REGEXP "^[A-Z][A-Z]?[1-9][0-9]? "
will match any row with a postcode field staring with one or two letters, followed by one or two digits, followed by a space. In other words, this example is looking for the valid format for a postcode, even though we can't be sure exactly what any of the letters or digits involved will be!