"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)
Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articlesS157 - More MySQL commands 
MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN - (2004-12-20) 
MySQL - Optimising Selects - (2004-12-21) 
Getting a list of unique values from a MySQL column - (2005-04-14) 
MySQL - a score of things to remember - (2005-11-12) 
SELECT in MySQL - choosing the rows you want - (2005-11-22) 
MySQL - JOIN or WHERE to link tables correctly? - (2005-12-01) 
MySQL - an FAQ - (2005-12-03) 
An occasional chance, and reducing data to manageable levels - (2005-12-04) 
Combining similar rows from a MySQL database select - (2006-01-17) 
Giving the researcher power over database analysis - (2006-01-22) 
Saving a MySQL query results to your local disc for Excel - (2006-01-29) 
Key facts - SQL and MySQL - (2006-02-04) 
Helicopter views and tartans - (2006-04-06) 
MySQL - the order of clauses and the order of actions - (2007-06-01) 
Outputting numbers as words - MySQL with Perl or PHP - (2007-06-17) 
MySQL joins revisited - (2007-09-03) 
Joining MySQL tables revisited - finding nonmatching records, etc - (2008-03-15) 
Finding words and work boundaries (MySQL, Perl, PHP) - (2008-08-03) 
Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23) 
MySQL - looking for records in one table that do NOT correspond to records in another table - (2009-03-31) 
Grouping rows for a summary report - MySQL and PHP - (2009-06-27) 
MySQL - efficiency and other topics - (2009-10-10) 
Relating tables with joins in MySQL - (2010-02-21) 
Counting rows in joined MySQL tables - (2010-02-22) 
Optimising and caching your MySQL enquiries - (2010-02-22) 
Removing duplicates from a MySQL table - (2010-02-22) 
Databases - why data is split into separate tables, and how to join them - (2010-11-20) 
SQL - Data v Metadata, and the various stages of data selection - (2011-04-29) 
Extracting data from backups to restore selected rows from MySQL tables - (2015-05-01)
Some other Articles
Commenting Perl regular expressionsIs enough enough?Accessing a page via POST from within a PHP scriptWheelchair access - can do!Matching in MySQLOut of the norm.Tomorrow's meeting.Up earlySticky newsDatabase or Progamming - which to learn first?