Mysql example of splitting into multiple rows and columns by specific symbols

Some fault code tables use the following design pattern for historical or performance reasons. That is, multiple attribute values ​​are stored in the same row or column. As shown in the tonly_error_record in the following table:

In this case, you can consider splitting the column by semicolon ";" to form multiple rows, and then splitting it by comma "," to form multiple columns. As shown in the following table:

This can be achieved using the string splitting function in MySQL. The function description is as follows:

SUBSTRING_INDEX(str,delim,count)   
-- str: the string to be split; delim: the delimiter; count: the number of times the delimiter appears

Finally, the specific implementation is as follows:

#Step 1: Split into multiple rows based on semicolon ";" #Step 2: Split into multiple columns based on comma "," select distinct S1.tbox_vin,
                (select substring_index(substring_index(S1.error_code, ',', 1), ',', -1)) as spn,
                (select substring_index(substring_index(S1.error_code, ',', 2), ',', -1)) fmi,
                S1.modify_time
from (
         select t1.tbox_vin,
                substring_index(substring_index(t1.dm1_string, ';', t2.help_topic_id + 1), ';', -1) as error_code,
                t1.modify_time
         from tonly_error_record t1
                  join mysql.help_topic t2
                       on t2.help_topic_id < (length(t1.dm1_string) - length(replace(t1.dm1_string, ';', '')) + 1)
         where t1.dm1_string is not null
           and t1.dm1_string != '') S1
where s1.error_code != ''
  and s1.error_code is not null
order by S1.modify_time desc;

Knowledge points involved

1. String splitting: SUBSTRING_INDEX (str, delim, count)

1. Parameter explanation

2. Examples

(1) Get all the characters before the second comma "," as the separator.

SUBSTRING_INDEX('7654,7698,7782,7788',',',2)

(2) Get all characters after the second to last comma separator ","

SUBSTRING_INDEX('7654,7698,7782,7788',',',-2) 

2. Replacement function: replace(str, from_str, to_str)

1. Parameter explanation

2. Examples

(1) Replace the separator "," comma with "" space.

REPLACE('7654,7698,7782,7788',',','')

3. Get the string length: LENGTH( str )

1. Parameter explanation

2. Example (1) Get the length of the string '7654,7698,7782,7788'

LENGTH('7654,7698,7782,7788') 

Implemented SQL parsing

SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1),',',-1) AS num 
FROM 
    mysql.help_topic 
WHERE 
    help_topic_id < LENGTH('7654,7698,7782,7788')-LENGTH(REPLACE('7654,7698,7782,7788',',',''))+1

Here, help_topic_id of the help_topic table of the MySQL library is used as a variable because help_topic_id is auto-incrementing. Of course, auto-incrementing fields of other tables can also be used as an aid.

help_topic table:

Implementation steps:

Step 1: First, get the number of strings that need to be split in the end, and use help_topic_id to simulate traversing the nth string.

The code snippet involved:

help_topic_id < LENGTH('7654,7698,7782,7788')-LENGTH(REPLACE('7654,7698,7782,7788',',',''))+1

Step 2: Split the string based on the comma "," using the SUBSTRING_INDEX (str, delim, count) function, and assign the result to the num field.

The code snippet involved:

SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1),',',-1) AS num

first step:

Use the comma "," as the delimiter and intercept all the strings before the n+1th delimiter according to the value of help_topic_id. (Here n+1 is because help_topic_id starts from 0, and here we need to get it from the first separator.)

SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1)

For example:
When help_topic_id = 0, the obtained string = 7654
When help_topic_id = 1, the obtained string = 7654,7698
…(and so on)

Step 2:

Use the comma "," as the delimiter and extract all the strings after the last delimiter.

SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1),',',-1)

For example:
According to the first step, when help_topic_id = 0, the obtained string = 7654, at this time the string intercepted in the second step = 7654
According to the first step, when help_topic_id = 1, the obtained string = 7654,7698, at this time the string intercepted in the second step = 7698
…(and so on)

Finally, the following effects were successfully achieved~

Note: For string splitting without delimiters, please refer to MySQL - String Splitting (String Interception without Delimiters)

This is the end of this article about examples of MySQL splitting into multiple rows and columns by specific symbols. For more information about MySQL specific symbol splitting, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!