MySQL query to fetch records with arrangement in the form of numbers and letter like 99S, 50K, etc.?

The easiest way to achieve this is by using REGEXP. Let us first create a table −

mysql> create table DemoTable
(
   Id varchar(50)
);
Query OK, 0 rows affected (0.77 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('John123');
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable values('123Chris');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('99Bob');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('19David');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('99S');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable values('10M');
Query OK, 1 row affected (0.14 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+----------+
| Id       |
+----------+
| John123  |
| 123Chris |
| 99Bob    |
| 19David  |
| 99S      |
| 10M      |
+----------+
6 rows in set (0.00 sec)

Following is the query to display records with arrangements in the form of numbers and letter −

mysql> select *from DemoTable where Id regexp '^[0-9][0-9][A-Z]$';

This will produce the following output −

+------+
| Id   |
+------+
| 99S  |
| 10M  |
+------+
2 rows in set (0.00 sec)

AmitDiwan

Updated on 03-Oct-2019 06:39:32

• Related Questions & Answers
• MySQL query for text search with LIKE and OR to fetch records
• Implement MySQL REGEXP to fetch records with . and numbers
• Find the records with % character in a LIKE query with MySQL
• MySQL query to find a match and fetch records
• MySQL query to fetch records from a range of months?
• Fetch maximum value from a column with values as string numbers like Value440, Value345, etc. in SQL
• MySQL REGEXP to fetch string + number records beginning with specific numbers?
• MySQL query to fetch the latest date from a table with date records
• MySQL query to fetch records before currentdate + 2 weeks?
• Use LIKE % to fetch multiple values in a single MySQL query
• MySQL query to fetch date with year and month?
• MySQL query to update all the values in a column with numeric incremental values like John1, John2, John3, etc.
• MySQL query to subtract date records with week day and display the weekday with records
• Order by numeric value from string records separated by numbers like CSE 15, CSE 11, etc.?
• MySQL LIKE query with dynamic array?