Home Accessibility Courses Diary The Mouth Facebook Resources Site Map About Us Contact
 
This week, we're updating our course layouts and descriptions. Presentation and materials always gently change over time, but just occasionally there's a need to make a step change to clear out some of the old and roll in the new. That's now happening - but over a long and complex site it's not instant and you'll see sections of the site changing up to and including 19th September.

See also [here] for status update
 
Finding words and work boundaries (MySQL, Perl, PHP)

If you're searching for the word "mile", you probably don't want the page that tells you that Sally Smiled at Harry. But you may want to find a Milestone, even if it is within quotes.

Regular Expressions are your friends!

In Perl style regular expressions (which also work in Python, and in PHP with the preg functions), the \b anchor (or 'zero width assertion') matches at a word boundary. In other words, it will let you find positions in your text string which have a special character followed by an alphanumeric, or an alphanumeric followed by a special character. It also matches at the very beginning or very end of the string if the string starts / ends with an alphanumeric.

example: /\bmile/i matches - ignoring case - words starting with mile.

In MySQL regular expressions (used with REGEXP and RLIKE matches), you have tow different anchors. [[:<:]] matches at the start of a word and [[:>:]] matches at the end of a word. Slightly longer / more complex, but probably a little quicker to run.

My personal suggestion - if you are searching - is to look for the search term anchoring the start but not the end to a word boundary. That way, you find all the "es" "ed" and "ing" words - end, ended, ending, but it does not send you round the bend with lots of spurious hits.

Our blog archive at http://www.wellho.net/mouth/ adds a column of "related short articles" down the right hand side to help you navigate to similar subjects. Until yesterday, we were reporting similar articles based on the subject of the current blog having one of its words appear within the subject line of another article - done that way from the early days of the blog to get a good spread of links to extra aricles. However, that list was getting long and I updated the script to use a MySQL regular expression - and now we have a list that (in most cases) has been trimmed back to a manageable size, and had a heightened relevance. For example - from 17 further links on that first page down to 12.

In other words - the clause
where entry_title like '%$word%'
has been replaced by
where entry_title rlike '[[:<:]]$word'

As an aside - we also eliminate a few common words from the page matching - here's the regular expression used on each word in a list from the title.

'^(the|from|and|you|our|why|they|via|that|can|all|'.
'use|your|big|how|etc|for|one|two|not|after|work|but|get|are)$'

(written 2008-08-03)

 
Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articles
H308 - PHP - Searches, and search engines
  [3163] Twitter - the special use of @ # and http: in tweets - (2011-02-09)
  [3159] Returning multiple values from a function call in various languages - a comparison - (2011-02-06)
  [2631] How to show a large result set page by page in PHP - (2010-02-11)
  [2166] Crossrefering documents with uniqueness and inconsistency issues - PHP proof of concept demo - (2009-05-10)
  [2137] Reaching the right people with your web site - (2009-04-23)
  [1020] Parallel processing in PHP - (2007-01-03)

P212 - Perl - More on Character Strings
  [3927] First match or all matches? Perl Regular Expressions - (2012-11-19)
  [3707] Converting codons via Amino Acids to Proteins in Perl - (2012-04-25)
  [3650] Possessive Regular Expression Matching - Perl, Objective C and some other languages - (2012-03-12)
  [3630] Serialsing and unserialising data for storage and transfer in Perl - (2012-02-28)
  [3546] The difference between dot (a.k.a. full stop, period) and comma in Perl - (2011-12-09)
  [3411] Single and double quotes strings in Perl - what is the difference? - (2011-08-30)
  [3332] DNA to Amino Acid - a sample Perl script - (2011-06-24)
  [3322] How much has Perl (and other languages) changed? - (2011-06-10)
  [3100] Looking ahead and behind in Regular Expressions - double matching - (2010-12-23)
  [3059] Object Orientation in an hour and other Perl Lectures - (2010-11-18)
  [2993] Arrays v Lists - what is the difference, why use one or the other - (2010-10-10)
  [2877] Further more advanced Perl examples - (2010-07-19)
  [2874] Unpacking a Perl string into a list - (2010-07-16)
  [2834] Teaching examples in Perl - third and final part - (2010-06-27)
  [2801] Binary data handling with unpack in Perl - (2010-06-10)
  [2657] Want to do a big batch edit? Nothing beats Perl! - (2010-03-01)
  [2379] Making variables persistant, pretending a database is a variable and other Perl tricks - (2009-08-27)
  [2230] Running a piece of code is like drinking a pint of beer - (2009-06-11)
  [1947] Perl substitute - the e modifier - (2008-12-16)
  [1727] Equality and looks like tests - Perl - (2008-07-29)
  [1510] Handling Binary data (.gif file example) in Perl - (2008-01-17)
  [1336] Ignore case in Regular Expression - (2007-09-08)
  [1305] Regular expressions made easy - building from components - (2007-08-16)
  [1251] Substitute operator / modifiers in Perl - (2007-06-28)
  [1230] Commenting a Perl Regular Expression - (2007-06-12)
  [1222] Perl, the substitute operator s - (2007-06-08)
  [943] Matching within multiline strings, and ignoring case in regular expressions - (2006-11-25)
  [928] C++ and Perl - why did they do it THAT way? - (2006-11-16)
  [737] Coloured text in a terminal from Perl - (2006-05-29)
  [608] Don't expose your regular expressions - (2006-02-15)
  [597] Storing a regular expression in a perl variable - (2006-02-09)
  [586] Perl Regular Expressions - finding the position and length of the match - (2006-02-02)
  [583] Remember to process blank lines - (2006-01-31)
  [453] Commenting Perl regular expressions - (2005-09-30)

Q803 - Object Orientation and General technical topics - Regular Expressions - Extra Elements
  [3516] Regular Expression modifiers in PHP - summary table - (2011-11-12)
  [3089] Python regular expressions - repeating, splitting, lookahead and lookbehind - (2010-12-17)
  [2909] Be gentle rather than macho ... regular expression techniques - (2010-08-08)
  [1860] Seven new intermediate Perl examples - (2008-10-30)
  [1613] Regular expression for 6 digits OR 25 digits - (2008-04-16)
  [1601] Replacing the last comma with an and - (2008-04-04)
  [1372] A taster PHP expression ... - (2007-09-30)

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)
  [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)
  [449] Matching in MySQL - (2005-09-24)
  [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
All around the world?
Previous and next
or
Horse's mouth home
Forward to
Current visitors from around the world - PHP
Some other Articles
Bath, Snake or Nag?
Clean code, jump free (Example in Lua)
Rules, suggestions, considerations for Lua variable names
Current visitors from around the world - PHP
Finding words and work boundaries (MySQL, Perl, PHP)
All around the world?
memcached - overview, installation, example of use in PHP
Old pictures and comparisons
Apache httpd, MySQL, PHP - installation procedure
Punting on the Cam
4287 posts, page by page
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, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86 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).

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2014: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/mouth/1735_Fin ... -PHP-.html • PAGE BUILT: Sun Mar 30 15:20:58 2014 • BUILD SYSTEM: WomanWithCat