字符串分割

SQLSERVER

CREATE FUNCTION [dbo].[Fun_SplitStr]
(
    @originalStr      VARCHAR(8000), --要分割的字符串
    @split varchar(100)  --分隔符号
)
RETURNS @temp TABLE(Result VARCHAR(100))
AS
BEGIN
    DECLARE @result AS VARCHAR(100);   --定义变量用于接收单个结果 
    
    SET @originalStr = @originalStr + @split ;  
      
    WHILE (@originalStr <> '')
    BEGIN
        SET @result = LEFT(@originalStr, CHARINDEX(@split, @originalStr, 1) -1) ;  
        
        INSERT @temp VALUES(@result) ;  
        
        --STUFF()函数用于删除指定长度的字符，并可以在指定的起点处插入另一组字符。
        SET @originalStr = STUFF(@originalStr, 1, CHARINDEX(@split, @originalStr, 1), '');
    END 
    RETURN
END
GO

MYSQL

CREATE DEFINER=`root`@`%` PROCEDURE `split`(IN _string VARCHAR(8000),StrSeprate varchar(10))
BEGIN
 # 求分割符号','的位置
DECLARE _index INT;
  
#使用临时表存储分割后的结果
DROP TEMPORARY TABLE IF EXISTS tmp_strs;
CREATE TEMPORARY TABLE tmp_strs(
str INT(10) UNSIGNED
);
  
SET _index = LOCATE(StrSeprate,_string);
WHILE _index > 0
DO
INSERT INTO tmp_strs VALUES(LEFT(_string,_index-1));#将子字符串存入临时表
SET _string =SUBSTR(_string FROM _index+1);
SET _index = LOCATE(StrSeprate,_string);
END WHILE;
  
IF LENGTH(_string) >= 0 THEN
INSERT INTO tmp_strs VALUES(_string);
select * from tmp_strs;
drop table tmp_strs;
END IF;
 
END

使用方法

select * 
from Fun_SplitStr((select pickname from users where username='123456'),',');