Checks if a string contains a number in MariaDB
This article discusses how to check if a string field contains a number in MariaDB.
Sometimes, you may need to check whether a string field contains characters or numbers. In MariaDB, the REGEXP operator can help you.
REGEXP is an operator used to verify that a string matches a specified regular expression.
Unlike LIKE, LIKE performs a full match, and REGEXP performs a partial match.
Examples
Suppose we have a notes table with the following rows:
SELECT * FROM notes;
Output:
+----+------------------------+
| id | note |
+----+------------------------+
| 1 | Hello World |
| 2 | This is for you, 2022. |
| 3 | AAAA |
| 4 | B-2-9-6 |
| 5 | 1234567890 |
+----+------------------------+Filter rows that contain numbers
The following statement finds rows that contain numbers in the note column:
SELECT *
FROM notes
WHERE note REGEXP '[0-9]+';
Output:
+----+------------------------+
| id | note |
+----+------------------------+
| 2 | This is for you, 2022. |
| 4 | B-2-9-6 |
| 5 | 1234567890 |
+----+------------------------+If you want to find those rows that only contain numbers, use the following statement:
SELECT *
FROM notes
WHERE note REGEXP '^[0-9]+$';
Output:
+----+------------+
| id | note |
+----+------------+
| 5 | 1234567890 |
+----+------------+Filter rows that also have letters
The following statement finds rows that contain letters in the note column:
SELECT *
FROM notes
WHERE note REGEXP '[a-zA-Z]+';
Output:
+----+------------------------+
| id | note |
+----+------------------------+
| 1 | Hello World |
| 2 | This is for you, 2022\. |
| 3 | AAAA |
| 4 | B-2-9-6 |
+----+------------------------+If you want to find rows that contain only letters, use the following statement:
SELECT *
FROM notes
WHERE note REGEXP '^[a-zA-Z]+$';
Output:
+----+------+
| id | note |
+----+------+
| 3 | AAAA |
+----+------+Conclusion
In this example, we use the REGEXP operator to find rows that match a regular expression pattern.