I imagine that if you are reading these lines you already know the regular expressions in SQL.
If so, in this article I am going to show you how to use the REGEXP operator in SQL and how it is used to search in columns with regular expressions.
A powerful function that is presented as an alternative to LIKE SQL or MATCH AGAINST SQL.
Content
- How do you do regular expressions in SQL?
- SQL REGEXP Syntax
- SQL REGEXP Examples
- Cheat Sheet Regexp SQL
How do you do regular expressions in SQL?
How do you know, regular expressions help us perform complex text searchesalways through patterns.
At least REGEXP allows us to perform much more complex searches what, for example, LIKE SQL offers us. Although I have to say that with a higher learning curve, since mastering regular expressions is usually a tough road.
SQL REGEXP Syntax
The syntax for using the REG EXP operator is simple:
SELECT * FROM tabla WHERE columna REGEXP 'expresión regular';
As you can see, I have made a standard SELECT, very simple, and I have simply used the WHERE operator followed by the pattern.
But what patterns can I use? I solve this question below, through SQL examples.
SQL REGEXP Examples
I’m going to start with simple examplessuch as “search for games with a certain word in the name”:
SELECT * FROM juego WHERE titulo REGEXP 'metal gear';
This query will find and return those records that contain in the title “metal gear”.
But what if I want it to start with “metal”?
SELECT * FROM juego WHERE titulo REGEXP '^metal gear';
Easy?
If you know regular expressions, the answer is surely a resounding YES.
If you don’t know them, nothing happens, then I leave you one chop or what we programmers call a Cheat Sheet.
Cheat Sheet Regexp SQL
Ok, we have it, you have already seen examples and now you want your Regexp CHEAT SHEET and the metacharacters that can be used.
You can load it at the following link: CheatSheet SQL REGEXP.
I leave it to you below:
Character | Explanation | Example of use |
* | The metacharacter asterisk is used to indicate the number of repetitions | allowed for the character it succeeds. Specifically, it indicates 0 or more. (which may or may not appear) SELECT * FROM game WHERE title REGEXP ‘assasins*’; |
will return all games containing “assasin” or “assasins”, as well as if there was a word that was assassinsss. | + The metacharacter more (+) | is similar to the asterisk, but this time indicates a minimum of 1 occurrence. That is, at least we want it to appear once. SELECT * FROM `game` WHERE `title` REGEXP‘mons+’ ; |
will return all sets containing “mons” or “monsss” characters. For example, Monster hunter. How time has indicated 1 or more “s”. | ? The question mark metacharacter (?) | It is used to indicate that we wait 0 or 1 times for the appearance of the symbol or the symbol it precedes. SELECT * FROM `game_category` WHERE` title` REGEXP ‘shoot?’; |
will return all categories that contain “shoot” or “shoo”, or what is the same “t appears 1 or 0 times” followed by “shoo”. | . The metacharacter spot (.) | is a wildcard that is used to refer to any character except the newline. SELECT * FROM game WHERE `year_released` REGEXP ‘200.’; |
[aBC] | will return all games released in the years starting with the characters “200” followed by any other numbers, letters, etc. For example, 2005,2007,2008 etc. The [abc] charlist | is used to match any of the characters included in the list, as if it were a “.” but only those indicated in brackets.[vwxyz]SELECT * FROM `game` WHERE` title` REGEXP ‘ ‘; |
[^abc] | will return all sets containing any character in lowercase “vwxyz”. For example, x-men, the witcher, etc. The [^abc] charlist | is used to indicate the opposite of the indicated symbols and characters.[^vwxyz]SELECT * FROM `game` WHERE` title` REGEXP ‘ ‘; |
[A-Z] | will return all sets that do not contain the characters “vwxyz”. [A-Z] He | is used to match any uppercase letter.[A-Z]SELECT * FROM `member` WHERE` name` REGEXP ‘ ‘; |
[a-z] | will return all members that have a name that contains the character A or Z in uppercase. For example, Álvaro or Zacarías. [a-z] He | used to match any lowercase letter[a-z]SELECT * FROM `members` WHERE` postal_address` REGEXP ‘ ‘; |
[0-9] | will give to all members who have postal addresses containing any character from a to z. . For example, Janet Jones with membership number 1. [0-9] He | It is used to join any digit from 0 to 9.[0-9]SELECT * FROM `members` WHERE` contact_number` REGEXP ‘ ‘[0-9]will give all members who have submitted contact numbers that contain characters “ |
”. For example, Robert Phil. | ^ He caret (^) | is used to indicate that the search should start with the next pattern. [cd]SELECT * FROM `game` WHERE` title` REGEXP ‘^ ‘; |
returns all games with the title starting with any of the characters in “cd”. For example, Code Name | | The vertical bar (|) | It is used to indicate alternatives.[cd]SELECT * FROM `game` WHERE` title` REGEXP ‘^[u]|^ ‘; |
[[:<:]returns all games with title beginning with either “cd” or “u” character. | ] [[:<:]He ] | is used to indicate that what follows must appear at the beginning of a word[[:<:]SELECT * FROM `game` WHERE` title` REGEXP ‘ ]m’; returns all games with titles that have any words beginning with “m”. For example: Devilmoh cry ,mMetal Gear Solid |
[[:>:]. | ] [[:>:]He ] | indicates that the pattern that follows it must appear at the end of some word[[:>:]SELECT * FROM `game` WHERE` title` REGEXP ‘s and ]’; returns all games that have “sy” at the end of a word like:fanta ending sy eitherphanta sy |
[[:clase:]star online | ] [[:clase:]The ] [[:alpha:]indicates that it should match a character class, i.e. ] [[:space:]to select letters, ] [[:digit:]to match the white space, ] [0-9] is for numbers, synonymous with [[:uppercase:]and | ]for uppercase letters.[[:alpha:]SELECT * FROM `game` WHERE` title` REGEXP ‘ ]’; gives all games with titles that contain only letters. For example,Metal Gear Solid but will not return |
Metal Gear Solid 4
With this Cheat Sheet
you can take a quick look at the possibilities of your REGEXP.
I hope you liked it. Share it and you will encourage me to improve this article and publish others so that we can help many more struggling web developers.
Thank you mille!