Parse a string to get a number from a large string separated by underscore

Let us first create a table −

mysql> create table DemoTable1961
   (
   Title text
   );
Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1961 values('You_can_remove_the_string_part_only-10001-But_You_can_not_remove_the_numeric_parts');
Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1961;

This will produce the following output −

+------------------------------------------------------------------------------------+
| Title                                                                              |
+------------------------------------------------------------------------------------+
| You_can_remove_the_string_part_only-10001-But_You_can_not_remove_the_numeric_parts |
+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Here is the query to implement CAST() and parse a string to get number from a string −

mysql> select cast(replace(replace('You_can_remove_the_string_part_only-10001-But_You_can_not_remove_the_numeric_parts','You_can_remove_the_string_part_only-',''),
   '-But_You_can_not_remove_the_numeric_parts','') as unsigned) as Output from DemoTable1961;

This will produce the following output −

+--------+
| Output |
+--------+
|  10001 |
+--------+
1 row in set (0.00 sec)