MySQL has a number of functions and operators that allow us to perform operations using regular expressions (regex). This article presents two operators and one function that enable us to find out if a string matches a regular expression specified by a given pattern.
These regex functions and operators are:
These are all basically equivalent, as the operators (the second two) are both synonyms of the function (the first one). In any case, you can see examples of all three in action below.
In MySQL, the MATCH() function performs a full-text search. It accepts a comma separated list of table columns to be searched.
The table/s must have a FULLTEXT index before you can do a full-text search against them (although boolean queries against a MyISAM search index can work — albeit slowly — even without a FULLTEXT index).
You can create a FULLTEXT index when creating the table (using the CREATE TABLE statement), or you can use the ALTER TABLE statement or the CREATE INDEX statement if the table already exists.
By default, the search is case-insensitive. To perform a case-sensitive search, use a case-sensitive or binary collation for the indexed columns.
MySQL includes a bunch of functions and operators that can help us when working with data.
The following is a list of string functions and operators available in MySQL. Click on each function or operator name to see an explanation of the function, its syntax, and examples.
In MySQL, the CHARACTER_LENGTH() function returns the length of a string, measured in characters.
CHARACTER_LENGTH() is a synonym for the CHAR_LENGTH() function.
In MySQL, you can use the SOUNDS LIKE operator to return results that sound like a given word.
This operator works best on strings in the English language (using it with other languages may return unreliable results).
In MySQL, you can use NOT LIKE to perform a negation of the LIKE operator. In other words, NOT LIKE returns the opposite result to LIKE.
If the string matches the pattern provided, the result is 0, otherwise it’s 1.
The pattern doesn’t necessarily need to be a literal string. This function can be used with string expressions and table columns.
In MySQL, the LIKE operator performs pattern matching using an SQL pattern.
If the string matches the pattern provided, the result is 1, otherwise it’s 0.
The pattern doesn’t necessarily need to be a literal string. This function can be used with string expressions and table columns.
In MySQL, the REGEXP_REPLACE() function replaces occurrences of the substring within a string that matches the given regular expression pattern.
The whole string is returned along with the replacements.
If there’s no match (i.e. the input string doesn’t contain the substring), the the whole string is returned unchanged.
In MySQL, the REGEXP_INSTR() function returns the starting index of a substring that matches the regular expression pattern.
The index starts at 1. If there’s no match, the result is 0.
In MySQL, the REGEXP_SUBSTR() function returns the substring that matches the given regular expression pattern.
If there’s no match (i.e. the input string doesn’t contain the substring), the result is NULL.