Home Accessibility Courses Twitter The Mouth Facebook Resources Site Map About Us Contact
 
For 2021 - online Python 3 training - see ((here)).

Our plans were to retire in summer 2020 and see the world, but Coronavirus has lead us into a lot of lockdown programming in Python 3 and PHP 7.
We can now offer tailored online training - small groups, real tutors - works really well for groups of 4 to 14 delegates. Anywhere in the world; course language English.

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

Q803 - Object Orientation and General technical topics - Regular Expressions - Extra Elements
  [943] Matching within multiline strings, and ignoring case in regular expressions - (2006-11-25)
  [1336] Ignore case in Regular Expression - (2007-09-08)
  [1372] A taster PHP expression ... - (2007-09-30)
  [1601] Replacing the last comma with an and - (2008-04-04)
  [1613] Regular expression for 6 digits OR 25 digits - (2008-04-16)
  [1860] Seven new intermediate Perl examples - (2008-10-30)
  [2909] Be gentle rather than macho ... regular expression techniques - (2010-08-08)
  [3089] Python regular expressions - repeating, splitting, lookahead and lookbehind - (2010-12-17)
  [3100] Looking ahead and behind in Regular Expressions - double matching - (2010-12-23)
  [3516] Regular Expression modifiers in PHP - summary table - (2011-11-12)
  [3650] Possessive Regular Expression Matching - Perl, Objective C and some other languages - (2012-03-12)

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

H308 - PHP - Searches, and search engines
  [1020] Parallel processing in PHP - (2007-01-03)
  [2137] Reaching the right people with your web site - (2009-04-23)
  [2166] Crossrefering documents with uniqueness and inconsistency issues - PHP proof of concept demo - (2009-05-10)
  [2631] How to show a large result set page by page in PHP - (2010-02-11)
  [3159] Returning multiple values from a function call in various languages - a comparison - (2011-02-06)
  [3163] Twitter - the special use of @ # and http: in tweets - (2011-02-09)
  [4401] Selecting RECENT and POPULAR news and trends for your web site users - (2015-01-19)


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
4759 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, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96 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., 2021: 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/mouth/1735_Fin ... -PHP-.html • PAGE BUILT: Sun Oct 11 16:07:41 2020 • BUILD SYSTEM: JelliaJamb