【MySQL】函数提取字符串中的数字

字符串中的数字"/>

【MySQL】函数提取字符串中的数字

实现：用 MySQL（5.7） 函数提取形如 “” 中的数字部分

思路：

①  把字符串 str0 反转得到 str1

②  - str1（字符型转整型） 得到 str2

③  - str2 得 str3，反转 str3 得到 str4

④  考虑到 str0 中会含有  ^.*[1-9]+0{n}$  格式的数据，要截取 str0 中自 str4 位置开始到 str0 结束的子字符串作为最终提取结果

相关 sql：

mysql> use test
Database changed
mysql> show tables;
Empty set (0.00 sec)mysql> create table test_reverse (-> id int unsigned not null auto_increment,-> url varchar(255) not null default '',-> primary key (id)-> ) engine = innodb default charset = utf8 collate = utf8_unicode_ci;
Query OK, 0 rows affected (0.34 sec)mysql> insert into test_reverse values (null, ''),(null, 'http://zpcode.
org/390');
Query OK, 2 rows affected (0.11 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from test_reverse;
+----+------------------------------+
| id | url                          |
+----+------------------------------+
|  1 |  |
|  2 |         |
+----+------------------------------+
2 rows in set (0.00 sec)mysql> alter table test_reverse add number_in_string int unsigned not null;
Query OK, 0 rows affected (0.65 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> select * from test_reverse;
+----+------------------------------+------------------+
| id | url                          | number_in_string |
+----+------------------------------+------------------+
|  1 |  |                0 |
|  2 |         |                0 |
+----+------------------------------+------------------+
2 rows in set (0.00 sec)mysql> update test_reverse set number_in_string = reverse(-(-reverse(url)));
Query OK, 2 rows affected (0.12 sec)
Rows matched: 2  Changed: 2  Warnings: 0mysql> select * from test_reverse;
+----+------------------------------+------------------+
| id | url                          | number_in_string |
+----+------------------------------+------------------+
|  1 |  |           826457 |
|  2 |         |               39 |
+----+------------------------------+------------------+
2 rows in set (0.00 sec)mysql> update test_reverse set number_in_string = substring(url, instr(url, number_in_string));
Query OK, 1 row affected (0.09 sec)
Rows matched: 2  Changed: 1  Warnings: 0mysql> select * from test_reverse;
+----+------------------------------+------------------+
| id | url                          | number_in_string |
+----+------------------------------+------------------+
|  1 |  |           826457 |
|  2 |         |              390 |
+----+------------------------------+------------------+
2 rows in set (0.00 sec)