SQL 根据指定字符拆分字符串

 

CREATE FUNCTION [dbo].[F_StringSplit]
(
    @STR NVARCHAR(MAX)='',
    @SPLITER NVARCHAR(20)='')
    RETURNS @TB TABLE(TB_V NVARCHAR(200))
        
    AS 
    
    
    BEGIN
        DECLARE @NUM INT, @POS INT,@NEXT_POS INT
        SET @NUM=0
        SET @POS=1
        WHILE (@POS <=LEN(@STR))
        BEGIN
            SELECT @NEXT_POS=CHARINDEX(@SPLITER,@STR,@POS)
            IF (@NEXT_POS=0 OR @NEXT_POS IS NULL)
            --BEGIN
                SELECT @NEXT_POS=LEN(@STR)+1
                
                INSERT INTO @TB VALUES(RTRIM(LTRIM(SUBSTRING(@STR,@POS,@NEXT_POS-@POS))))
                
                SELECT @POS=@NEXT_POS +1
            --END
            
        END
        RETURN
    END
 

调用：

select * from  DBO.F_StringSplit('5,6,2,3,',',')