How to separate strings with delimiters in MySQL

MySQL separates strings with delimiters

Using

you can use the following functions

SUBSTRING_INDEX( i.final_car_type, ' ', 1 )

• i.final_car_type is the string that needs to be separated

• ’ ’ That is, separate the string with spaces

• 1 That is: take out All characters before the space

If count is a positive number, the result is everything to the left of the Nth separator from left to right. If it is a negative number, it means counting from the right, taking all the content on the right side of the Nth separator

Effect

If count is 1

• Table and internal data: Dongfeng Fengshen’s new AX7 Mach version DF 21 (sunroof version)

• After split: Dongfeng Fengshen’s new AX7 Mach version

If count is -1

• Table and internal data: Dongfeng Fengshen new AX7 Mach version DF 21 (sunroof version)

• After splitting: 21 (Skylight version)

MySQL stored procedure splits the string according to the delimiter

DELIMITER $$
 
CREATE DEFINER=`root`@`%` PROCEDURE `proc_split_Id`(in selectIds blob(65535),
in splitChar varchar(2))
BEGIN
set @i=0; 
CREATE TEMPORARY TABLE if not exists Id_Result_s(Id long NOT NULL);
truncate table Id_Result_s;
SET @cnt = 1+(LENGTH(selectIds) - LENGTH(REPLACE(selectIds,splitChar,''))); 
set @i=1; 
	start transaction;
    WHILE @i <=@cnt DO          
        SET @result = REPLACE(SUBSTRING(SUBSTRING_INDEX(selectIds, splitChar, @i),
       LENGTH(SUBSTRING_INDEX(selectIds, splitChar, @i -1)) + 1),
       splitChar, &#39;&#39;);  
       INSERT INTO Id_Result_s(Id) VALUES (@result );
		SET @i = @i + 1;   
    END WHILE;  
	commit;    
END

selectIds needs to be split split string, splitChar is the delimiter. The split result is stored in the temporary table Id_Result_s.

There is an insert statement in while, which is inserted in a loop. Adding start transaction and commit before and after while can improve efficiency.

The above is the detailed content of How to separate strings with delimiters in MySQL. For more information, please follow other related articles on the PHP Chinese website!