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

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

Recommend

Linux kernel device driver kernel debugging technical notes collation

/****************** * Kernel debugging technology...

Example of how to configure nginx to implement SSL

Environmental Description Server system: Ubuntu 1...

Vue backend management system implementation of paging function example

This article mainly introduces the implementation...

A simple method to merge and remove duplicate MySQL tables

Scenario: The crawled data generates a data table...

Comparison of 5 CSS scrolling ceiling implementation methods (performance upgrade version)

Preview of revised version This article was writt...

CSS to achieve pop-up window up and down center left and right and transparent background lock window effect

There is a simple CSS method to realize the pop-u...

Discussion on the way to open website hyperlinks

A new window opens. Advantages: When the user cli...

Example code of html formatting json

Without further ado, I will post the code for you...

MAC+PyCharm+Flask+Vue.js build system

Table of contents Configure node.js+nvm+npm npm s...

Example code for using CSS cross-fade() to achieve a semi-transparent background image effect

1. Requirements description For a certain element...

Detailed steps to configure my.ini for mysql5.7 and above

There is no data directory, my-default.ini and my...

Detailed application of Vue dynamic form

Overview There are many form requirements in the ...

Dynamically add tables in HTML_PowerNode Java Academy

Without further ado, I will post the code for you...

Explanation of Linux kernel optimization configuration for high-concurrency nginx server

Since the default Linux kernel parameters are bas...

Application of Hadoop counters and data cleaning

Data cleaning (ETL) Before running the core busin...