if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetStr]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_GetStr]
GO
--分段截取函数
CREATE FUNCTION dbo.f_GetStr(
@s varchar(8000),      --包含多个数据项的字符串
@pos int,             --要获取的数据项的位置
@split varchar(10)     --数据分隔符
)RETURNS varchar(100)
AS
BEGIN
    IF @s IS NULL RETURN(NULL)
    DECLARE @splitlen int
    SELECT @splitlen=LEN(@split+'a')-2
    WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0
        SELECT @pos=@pos-1,
            @s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'')
    RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),''))
END
GO

select dbo.f_GetStr('2∮11∮10∮09∮10∮13∮786∮91.93∮69∮100.00 ',7,'∮')
/*
----------------------------------------------------------------
786

(1 個資料列受到影響)

------解决方案--------------------

SQL code

DECLARE @value VARCHAR(1000)
DECLARE @sql VARCHAR(1000)
DECLARE @int INT 

SET @sql = ''
SET @int = 1
SET @value = '11111\22222\33333\44'

WHILE CHARINDEX('\',@value) > 0
BEGIN
    SELECT @value = STUFF(@value,CHARINDEX('\',@value),1,' AS Field' + RTRIM(@int) + ', '),@int = @int + 1
END
SET @sql = 'SELECT ' + @value + ' AS Field' + RTRIM(@int)
EXEC (@sql)

Field1    Field2    Field3    Field4
11111    22222    33333    44