根据分隔符的位置获取值

GO
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID('fn_GetValueBySepPos') AND XTYPE='FN')
BEGIN
    DROP FUNCTION dbo.fn_GetValueBySepPos
END
GO
--功能：根据分隔符的位置获取值
--参数：@String 输入的字符串；@Sep 分隔符(默认为逗号)；@Pos 分隔符的位置(默认为第一个分隔符)；@BeforeOrAfter  (0 表示前面，1表示后面)取指定位置分隔符前面（后面）的字符串(默认取分隔符前面的字符串)
CREATE FUNCTION dbo.fn_GetValueBySepPos
(
    @String VARCHAR(MAX),
    @Sep VARCHAR(10),
    @Pos INT,
    @BeforeOrAfter INT
)
RETURNS VARCHAR(MAX)
AS 
BEGIN
    DECLARE @Result VARCHAR(MAX)
    SELECT @Result = '',@String = ISNULL(@String,''),@Sep = ISNULL(@Sep,','),@Pos = ISNULL(@Pos,1),@BeforeOrAfter=ISNULL(@BeforeOrAfter,0)
    
    IF @String = ''
        BEGIN
            RETURN @Result
        END
    
    DECLARE @temp TABLE
    (
        Rn INT IDENTITY(1,1),
        Value VARCHAR(MAX)
    )
    INSERT INTO @temp( Value )    
    SELECT Value 
    FROM dbo.fn_Split1(@String,@Sep)
    
    IF @BeforeOrAfter=0
        BEGIN
            --前面
            SELECT @Result = STUFF((SELECT TOP (@Pos) @Sep + Value FROM @temp ORDER BY Rn ASC FOR XML PATH('')),1,1,'')
        END
    ELSE
        BEGIN
            --后面
            SELECT @Result = STUFF((SELECT TOP (@Pos) @Sep + Value FROM @temp ORDER BY Rn DESC FOR XML PATH('')),1,1,'')
        END
    
    RETURN @Result
END
GO