字符串中的数字"/>
【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)
更多推荐
【MySQL】函数提取字符串中的数字
发布评论