MYSQL Matching Exact String

Matching Exact String With mySQL

MYSQL allows you to use several methods to find a precise string in a database column. A quick method is to use the LIKE ‘%mystring%’. This method will return any string which begins or ends with mystring. This can be quite useful for searching, but, if you want to output data that only has the precise match of ‘mystring’ in a sentence, then, you will need to find a regular expression.

REGEXP

The sample coding below will be used to modify all characters in a column to lowercase while the REGEXP operator will then find the match. The object of this exercise is to gather all articles from two categories that contain the text ‘OOP’ or ‘object oriented programming’ and display them with links back to their articles.

As you can see, mySQL uses ‘[[:space:]]’ as whitespace; unlike the usual \s* you would use in PHP or PERL. You may want to note that if you added ‘[[:space:]]’ in front of the word ‘object’, you would not select the data from the fulltext field if there were html characters in front of the object word. An example of characters in front would be an article that had a link on the words object oriented programming.

$command = "SELECT bc.id, bc.title, bc.alias as alias, bc.created as created, pl.permalink as permalink FROM prefix_content as bc, prefix_categories as bcat, prefix_myblog_permalinks as pl WHERE bc.sectionid='6' AND bc.catid IN(70,83) AND bcat.id=bc.catid AND (LOWER(bc.fulltext) REGEXP '[[:<:]]oop[[:>:]]' || LOWER(bc.fulltext) REGEXP 'object[[:space:]]oriented[[:space:]]programming' ) AND bc.state = 1 AND pl.contentid=bc.id ORDER BY bc.created DESC";
$result = mysqli_query($db, $command);

August 14, 2014 by kent