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)
Published on 02-Jan-2020 05:52:45
