"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 09:54:57)

 S157 - More MySQL Commands

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?
Link to page ... 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33 at 50 posts per page


This is a page archived from The Horse's Mouth at http://www.wellho.net/horse/ - the diary and writings of Graham Ellis. Every attempt was made to provide current information at the time the page was written, but things do move forward in our business - new software releases, price changes, new techniques. Please check back via our main site for current courses, prices, versions, etc - any mention of a price in "The Horse's Mouth" cannot be taken as an offer to supply at that price.

Link to Ezine home page (for reading).
Link to Blogging home page (to add comments).