文字列内の数値を抽出するMySQL関数

Mysql Function Extract Numbers String

成し遂げる： MySQL関数を使用して、フォームの数値部分を抽出します 'http://www.xxx.com/hotel/detail/826457'

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, 'http://www.zpcode.com/826457'),(null, 'http://zpcode. org/390') Query OK, 2 rows affected (0.11 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from test_reverse +----+------------------------------+ | id | url | +----+------------------------------+ | 1 | http://www.zpcode.com/826457 | | 2 | http://zpcode.org/390 | +----+------------------------------+ 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: 0 mysql> select * from test_reverse +----+------------------------------+------------------+ | id | url | number_in_string | +----+------------------------------+------------------+ | 1 | http://www.zpcode.com/826457 | 0 | | 2 | http://zpcode.org/390 | 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: 0 mysql> select * from test_reverse +----+------------------------------+------------------+ | id | url | number_in_string | +----+------------------------------+------------------+ | 1 | http://www.zpcode.com/826457 | 826457 | | 2 | http://zpcode.org/390 | 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: 0 mysql> select * from test_reverse +----+------------------------------+------------------+ | id | url | number_in_string | +----+------------------------------+------------------+ | 1 | http://www.zpcode.com/826457 | 826457 | | 2 | http://zpcode.org/390 | 390 | +----+------------------------------+------------------+ 2 rows in set (0.00 sec)