MySQL string splitting example (string extraction without separator)

String extraction without delimiters

Question Requirements

Field value in the database:

Implementation effect: Need to turn one row of data into multiple rows

Implemented SQL

SELECT LEFT(SUBSTRING('P1111',help_topic_id+1),1) AS num FROM mysql.help_topic WHERE help_topic_id < LENGTH('P1111');

Knowledge points involved

1. String interception: SUBSTRING(str,pos)

1. Parameter Description

2. Examples

(I) Get the string 'P1111' starting from the second character

SUBSTRING('P1111',2)

(II) Get the string 'P1111' starting from the second to last character

SUBSTRING('P1111',-2)

2. Extract the string from the left: LEFT(str,len)

1. Parameter Description

2. Examples

(I) Get the leftmost 3 characters of the string 'P1111'

LEFT('P1111',3)

Tip:

There is a function LEFT(str,len) that intercepts the string from the left, and of course there is also a RIGHT(str,len) that intercepts the string from the right. The principles of the two functions are the same, the difference is whether the string is intercepted from the left or the right.

SQL Parsing

SELECT LEFT(SUBSTRING('P1111',help_topic_id+1),1) AS num FROM mysql.help_topic WHERE help_topic_id < LENGTH('P1111');

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

Please refer to MySQL - String Splitting (I)

Implementation steps

Step 1:

Get the length of the string 'P1111' and use help_topic_id to dynamically simulate the traversal of the string 'P1111'

help_topic_id < LENGTH('P1111')

Step 2:

Use the SUBSTRING(str,pos) function and help_topic_id to extract the string 'P1111'. (The reason for "help_topic_id+1" here is that help_topic_id starts at 0, and the SUBSTRING function needs to intercept the string from the first position)

SUBSTRING('P1111',help_topic_id+1)

For example:

When help_topic_id = 0, the obtained string = P1111

When help_topic_id = 1, the obtained string = 1111

…(and so on)

Step 3: Based on the implementation of step 2, use the LEFT(str,len) function to get the first character from the left in step 2.

LEFT(SUBSTRING('P1111',help_topic_id+1),1)

For example:

According to step 2, when help_topic_id = 0, the string obtained = P1111, and the string obtained in step 3 = P

According to step 2, when help_topic_id = 1, the string obtained = 1111, and the string obtained in step 3 = 1

…(and so on)

Finally, the following effects were successfully achieved

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

Supplement: mysql splits the found string_Mysql split string query

I won't say much nonsense, let's just look at the code~

DELIMITER $$
DROP FUNCTION IF EXISTS `tms1`.`GetClassName` $$
CREATE FUNCTION `GetClassName`(f_string VARCHAR(15000)) RETURNS varchar(15000)
BEGIN
/* Check the first position of the string containing ,*/
DECLARE THE_CNT INT(15) DEFAULT 1;
/* Class number */
declare classId varchar(20) default '';
/* Returned class name */
DECLARE result varchar(15000) DEFAULT null;
/* Class name */
DECLARE className varchar(50) DEFAULT '';
/* The first position of the string containing ,*/
set THE_CNT = LOCATE(',',f_string);
/* Check if the first position of the string contains , exists*/
while (THE_CNT >= 0) do
/* , when the location does not exist*/
if THE_CNT = 0 then
/* Setting of class number*/
set classId = f_string;
else
/* Get the class number from the string */
set classId = SUBSTRING_INDEX(SUBSTRING_INDEX(f_string, ',', 1), ',', -1);
end if ;
/* Get the class name based on the class number */
select (select name from class where id = classId) into className;
/* Returns the class number string when it is empty*/
if result is null then
/* When the class name is not found according to the number*/
if className is null then
/* Set the class name to empty */
set className = ' ';
end if;
/* Append the class name to the string */
set result = className;
else
/* When the class name is not found according to the number*/
if className is null then
/* Set the class name to empty */
set className = ' ';
end if;
/* Append the class name to the string */
set result = CONCAT(result,',',className);
end if;
/* , when the location does not exist*/
if THE_CNT = 0 then
/* Return result set */
return result;
end if;
/*Intercept the incoming string*/
set f_string = right(f_string,length(f_string) - THE_CNT);
/* The first position of the string containing ,*/
set THE_CNT = LOCATE(',',f_string);
/* End traversal */
end while;
/* Return result set */
return result;
END $$
DELIMITER ;

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.