mysql split函数代码二例

DELIMITER $$

DROP FUNCTION IF EXISTS `f_split_varchar`$$

CREATE
    FUNCTION `f_split_varchar`(f_string VARCHAR(1000),f_delimiter VARCHAR(5)) RETURNS  INT(11)
   
    BEGIN
    DECLARE returnInt INT(11); 
      IF LENGTH(f_delimiter) = 2  THEN 
         RETURN 1+(LENGTH(f_string) - LENGTH(REPLACE(f_string,f_delimiter,'')))/2; 
      ELSE     
         RETURN 1+(LENGTH(f_string) - LENGTH(REPLACE(f_string,f_delimiter,''))); 
      END IF; 

    END$$

DELIMITER ;

SELECT ID FROM DBO.FUN_SPLIT('1,2,3,4,5,6',',')
CREATE FUNCTION [dbo].[FUN_SPLIT]( 
    @STR VARCHAR(8000), 
    @FLAG VARCHAR(8000) 
) 
RETURNS @RE TABLE(ID VARCHAR(8000)) 
AS 
BEGIN    
    WHILE CHARINDEX(@FLAG,@STR)>0 
    BEGIN 
        INSERT INTO @RE 
        SELECT SUBSTRING(@STR,0,CHARINDEX(@FLAG,@STR)) 
        SELECT @STR = SUBSTRING(@STR,CHARINDEX(@FLAG,@STR)+1,LEN(@STR)) 
    END 
    INSERT INTO @RE 
    SELECT @STR 
    RETURN; 
END