How to extract string elements from non-fixed positions in MySQL

Preface

Note: The test database version is MySQL 8.0

Test data:

create table zqs(id int,str varchar(1000));

insert into zqs(id,str) values (1,'【JD.com】abc【China Telecom】');
insert into zqs(id,str) values (1,'【JD.com】abc【China Telecom】def');
insert into zqs(id,str) values (1,'****【JD.com】abc【China Telecom】def');
insert into zqs(id,str) values (1,'****【JD.com】abc');
insert into zqs(id,str) values (1,'【JD.com】abc【China Telecom】【China Unicom】');

1. Demand

We often encounter similar requirements as SMS, and need to extract SMS tag information, but there may be multiple SMS tags.

This example assumes that there are at most 3 tags and needs to be output as follows:

mysql> select * from zqs;
±-----±----------------------------------------------------+
| id | str |
±-----±----------------------------------------------------+
| 1 | 【JD.com】abc【China Telecom】 |
| 1 | 【JD.com】abc【China Telecom】def |
| 1 | ****【JD.com】abc【China Telecom】def |
| 1 | ****【JD.com】abc |
| 1 | 【JD.com】abc【China Telecom】【China Unicom】 |
±-----±----------------------------------------------------+

The required output is as follows:

±-------------±-------------------±-------------------+
| first_val | first_va2 | first_va3 |
±-------------±-------------------±-------------------+
| 【JD.com】 | 【China Telecom】 | |
| 【JD.com】 | 【China Telecom】 | |
| 【JD.com】 | 【China Telecom】 | |
| 【JD.com】 | | |
| 【JD.com】 | 【China Telecom】 | 【China Unicom】 |
±-------------±-------------------±-------------------+

2. Solution

Oracle string interception functions substr and instr can be used together, but MySQL's instr function is weaker than Oracle's instr function.

At this time, you need to use the MySQL regular expression regexp_instr function and substr function

select substr(str,
   regexp_instr(str,'【',1,1),
   regexp_instr(str,'】',1,1) - regexp_instr(str,'【',1,1) + 1 ) first_val, 
  substr(str,
   regexp_instr(str,'【',1,2),
   regexp_instr(str,'】',1,2) - regexp_instr(str,'【',1,2) + 1) first_va2, 
  substr(str,
   regexp_instr(str,'【',1,3),
   regexp_instr(str,'】',1,3) - regexp_instr(str,'【',1,3) + 1) first_va3
 from zqs;

Testing Log:

mysql> select substr(str,
 -> regexp_instr(str,'【',1,1),
 -> regexp_instr(str,'】',1,1) - regexp_instr(str,'【',1,1) + 1 ) first_val,
 -> substr(str,
 -> regexp_instr(str,'【',1,2),
 -> regexp_instr(str,'】',1,2) - regexp_instr(str,'【',1,2) + 1) first_va2,
 -> substr(str,
 -> regexp_instr(str,'【',1,3),
 -> regexp_instr(str,'】',1,3) - regexp_instr(str,'【',1,3) + 1) first_va3
 -> from zqs;
+--------------+--------------------+--------------------+
| first_val | first_va2 | first_va3 |
+--------------+--------------------+--------------------+
| 【JD.com】 | 【China Telecom】 | |
| 【JD.com】 | 【China Telecom】 | |
| 【JD.com】 | 【China Telecom】 | |
| 【JD.com】 | | |
| 【JD.com】 | 【China Telecom】 | 【China Unicom】 |
+--------------+--------------------+--------------------+
5 rows in set (0.00 sec)

Summarize

This is the end of this article about how to extract string elements from non-fixed positions in MySQL. For more information about extracting string elements from MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

<<: Vue implements a simple marquee effect

>>: Example code for implementing ellipse trajectory rotation using CSS3