"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!
(written 2005-09-24, updated 2006-06-05)

 S157 - More MySQL commands
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [3061] Databases - why data is split into separate tables, and how to join them - (2010-11-20)
  [2647] Removing duplicates from a MySQL table - (2010-02-22)
  [2645] Optimising and caching your MySQL enquiries - (2010-02-22)
  [2644] Counting rows in joined MySQL tables - (2010-02-22)
  [2643] Relating tables with joins in MySQL - (2010-02-21)
  [2448] MySQL - efficiency and other topics - (2009-10-10)
  [2259] Grouping rows for a summary report - MySQL and PHP - (2009-06-27)
  [2110] MySQL - looking for records in one table that do NOT correspond to records in another table - (2009-03-31)
  [1904] Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23)
  [1735] Finding words and work boundaries (MySQL, Perl, PHP) - (2008-08-03)
  [1574] Joining MySQL tables revisited - finding nonmatching records, etc - (2008-03-15)
  [1331] MySQL joins revisited - (2007-09-03)
  [1235] Outputting numbers as words - MySQL with Perl or PHP - (2007-06-17)
  [1213] MySQL - the order of clauses and the order of actions - (2007-06-01)
  [673] Helicopter views and tartans - (2006-04-06)
  [591] Key facts - SQL and MySQL - (2006-02-04)
  [581] Saving a MySQL query results to your local disc for Excel - (2006-01-29)
  [572] Giving the researcher power over database analysis - (2006-01-22)
  [567] Combining similar rows from a MySQL database select - (2006-01-17)
  [517] An occasional chance, and reducing data to manageable levels - (2005-12-04)
  [515] MySQL - an FAQ - (2005-12-03)
  [513] MySQL - JOIN or WHERE to link tables correctly? - (2005-12-01)
  [502] SELECT in MySQL - choosing the rows you want - (2005-11-22)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [279] Getting a list of unique values from a MySQL column - (2005-04-14)
  [159] MySQL - Optimising Selects - (2004-12-21)
  [158] MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN - (2004-12-20)

Back to Out of the norm.

Previous and next or Horse's mouth home

Forward to Wheelchair access - can do!

Commenting Perl regular expressions
Is enough enough?
Accessing a page via POST from within a PHP script
Wheelchair access - can do!
Matching in MySQL
Out of the norm.
Tomorrow's meeting.
Up early
Sticky news
Database or Progamming - which to learn first?