Fetch a specific record from a column with string values (string, numbers and special characters) in MySQL

For this, you can use ORDER BY CAST(). Let us see an example −

mysql> create table DemoTable2006
(
   UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   UserCode    varchar(20)
);
Query OK, 0 rows affected (1.14 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable2006(UserCode) values('John_12');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable2006(UserCode) values('John_34');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable2006(UserCode) values('John_56');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable2006(UserCode) values('Chris_101');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable2006(UserCode) values('Chris_103');
Query OK, 1 row affected (0.37 sec)
mysql> insert into DemoTable2006(UserCode) values('Chris_106');
Query OK, 1 row affected (0.07 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable2006;

This will produce the following output −

+--------+-----------+
| UserId | UserCode  |
+--------+-----------+
|      1 | John_12   |
|      2 | John_34   |
|      3 | John_56   |
|      4 | Chris_101 |
|      5 | Chris_103 |
|      6 | Chris_106 |
+--------+-----------+
6 rows in set (0.00 sec)

Here is the query to fetch a specific record from a column −

mysql> select * from DemoTable2006
   where UserCode like 'John%'
   order by cast(substring(UserCode from 7) as  signed) desc
   limit 1;

This will produce the following output −

+--------+----------+
| UserId | UserCode |
+--------+----------+
|      3 | John_56  |
+--------+----------+
1 row in set (0.00 sec)

AmitDiwan

Updated on 02-Jan-2020 05:55:39

• Related Questions & Answers
• MySQL regular expression to update a table with column values including string, numbers and special characters
• MySQL query to select a specific string with special characters
• Fetch specific rows from a MySQL table with duplicate column values (names)?
• Return only the first 15 characters from a column with string values in MySQL
• MySQL query to remove special characters from column values?
• PHP fetch a specific value that begins with a given number from a string with letters and numbers?
• MySQL REGEXP to fetch string + number records beginning with specific numbers?
• Search for specific characters within a string with MySQL?
• Fetch maximum value from a column with values as string numbers like Value440, Value345, etc. in SQL
• Search a string with special characters in a MongoDB document?
• Append special characters to column values in MySQL
• Fetch a specific record using a single MySQL query with AND & OR operator
• MySQL query to retrieve only the column values with special characters?
• MySQL CONCAT a specific column value with the corresponding record
• Two ways to fetch maximum value from a MySQL column with numbers