Get digits from a record in MySQL?

Use the function CONVERT() or Regular Expression. The CONVERT() method converts a value from one datatype to another. This will ecnetually fetch digits for us. Let us see an example.

Firstly, we will create a table.

 mysql> create table textIntoNumberDemo   -> (   -> Name varchar(100)   -> ); Query OK, 0 rows affected (0.47 sec) 

Inserting some records.

 mysql> insert into textIntoNumberDemo values('John-11'); Query OK, 1 row affected (0.11 sec) mysql> insert into textIntoNumberDemo values('John-12'); Query OK, 1 row affected (0.17 sec) mysql> insert into textIntoNumberDemo values('John-2'); Query OK, 1 row affected (0.11 sec) mysql> insert into textIntoNumberDemo values('John-4'); Query OK, 1 row affected (0.14 sec) 

To display all records.

 mysql> select *from textIntoNumberDemo; 

Here is the output.

 +---------+ | Name    | +---------+ | John-11 | | John-12 | | John-2  | | John-4  | +---------+ 4 rows in set (0.00 sec) 

Syntax to fetch digit.

 SELECT yourColumnName,CONVERT(SUBSTRING_INDEX(yourColumnName,'-',-1),UNSIGNED INTEGER) AS  yourVariableName FROM yourTableName order by yourVariableName; 

The following is the query.

 mysql> SELECT Name,CONVERT(SUBSTRING_INDEX(Name,'-',-1),UNSIGNED INTEGER) AS MyNumber   -> FROM textIntoNumberDemo   -> order by MyNumber; 

Here is the output.

 +---------+----------+ | Name    | MyNumber | +---------+----------+ | John-2  | 2 | | John-4  | 4 | | John-11 |       11 | | John-12 |       12 | +---------+----------+ 4 rows in set (0.00 sec)