Please wait while we load your page...


Latest Version [8.00202307241] Last Updated [Jul-24-2023]

PasswordSentry Highly Recommends NETbilling

Using Regular Expressions to Search

mySQL, being the powerfull database system it is (and like programming languages including Perl, PHP, JavaScript, etc.), provides the capability to use regular expressions (regex) when selecting (or searching) data. This feature allows you to search for certain data elements, and limit your results. For example, only display data records for a given user. The basic regex syntax is as follows:

SELECT `value` FROM `tablename` WHERE `columnname` REGEXP 'regexp'

For example, to select all columns from the table, users, where the values in the column, date, end with 2015, execute the following mySQL query:

SELECT * FROM `users` WHERE `date` REGEXP '2015$'

The $ character is a metacharacter. Metacharacters instruct mySQL how to define the search. In this case, the $ metacharacter tells mySQL to select columns that end with 2015. Below you will see a list of the mySQL regular expression metacharacters:

. match any character ? match zero or one
* match zero or more
+ match one or more
{n} match n times
{m,n} match m through n times
{n,} match n or more times
^ beginning of line
$ end of line
[[:<:]] match beginning of words
[[:>:]] match ending of words
[:alpha:] for letters
[:space:] for whitespace (space, tab, newline, and carriage return)
[:punct:] for punctuation
[:lower:] for lower case letters
[:upper:] for upper case letters
[abc] match one of enclosed chars
[^xyz] match any char not enclosed
| separates alternatives

Note that mySQL interprets a backslash () character as an escape character. To use a backslash in a regular expression, you must escape it with another backslash (\).

For a complete guide, check the MySQL Documentation.
Blog Posts

Random Security Blog Articles

See All Blog Posts

PasswordSentry :: Security Blog Article 1. Introduction SQL injection is a type of attack that allows an attacker to execute malicious SQL code on a database. This code can be used to view, modify, or delete data from the database. SQL injection attacks are a … Read More
PasswordSentry :: Security Blog ArticleColonial Pipeline storage tanks. On May 7, 2021, the company experienced a ransomware cyberattack. (AP Photo/Seth Wenig) Michael Parent, Simon Fraser University and David R. Beatty, University of Toronto Make no mistake: We are also in the midst … Read More

Live Chat Offline