关于SQL的字符串处理

-- 各种字符串分函数

if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[f_splitSTR] ' ) and xtype in (N ' FN ' , N ' IF ' , N ' TF ' ))
drop function [ dbo ] . [ f_splitSTR ]
GO

-- 3.2.1 循环截取法
CREATE FUNCTION f_splitSTR(
@s    varchar ( 8000 ),   -- 待分拆的字符串
@split varchar ( 10 )     -- 数据分隔符
) RETURNS @re TABLE (col varchar ( 100 ))
AS
BEGIN
    DECLARE @splitlen int
    SET @splitlen = LEN ( @split + ' a ' ) - 2
    WHILE CHARINDEX ( @split , @s ) > 0
    BEGIN
        INSERT @re VALUES ( LEFT ( @s , CHARINDEX ( @split , @s ) - 1 ))
        SET @s = STUFF ( @s , 1 , CHARINDEX ( @split , @s ) + @splitlen , '' )
    END
    INSERT @re VALUES ( @s )
    RETURN
END
GO


/* ============================================== */

if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[f_splitSTR] ' ) and xtype in (N ' FN ' , N ' IF ' , N ' TF ' ))
drop function [ dbo ] . [ f_splitSTR ]
GO

-- 3.2.3.1 使用临时性分拆辅助表法
CREATE FUNCTION f_splitSTR(
@s    varchar ( 8000 ),  -- 待分拆的字符串
@split varchar ( 10 )     -- 数据分隔符
) RETURNS @re TABLE (col varchar ( 100 ))
AS
BEGIN
    -- 创建分拆处理的辅助表(用户定义函数中只能操作表变量)
    DECLARE @t TABLE (ID int IDENTITY ,b bit )
    INSERT @t (b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b

    INSERT @re SELECT SUBSTRING ( @s ,ID, CHARINDEX ( @split , @s + @split ,ID) - ID)
    FROM @t
    WHERE ID <= LEN ( @s + ' a ' )
        AND CHARINDEX ( @split , @split + @s ,ID) = ID
    RETURN
END
GO

/* ============================================== */

if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[f_splitSTR] ' ) and xtype in (N ' FN ' , N ' IF ' , N ' TF ' ))
drop function [ dbo ] . [ f_splitSTR ]
GO

if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[tb_splitSTR] ' ) and objectproperty (id,N ' IsUserTable ' ) = 1 )
drop table [ dbo ] . [ tb_splitSTR ]
GO

-- 3.2.3.2 使用永久性分拆辅助表法
-- 字符串分拆辅助表
SELECT TOP 8000 ID = IDENTITY ( int , 1 , 1 ) INTO dbo.tb_splitSTR
FROM syscolumns a,syscolumns b
GO

-- 字符串分拆处理函数
CREATE FUNCTION f_splitSTR(
@s      varchar ( 8000 ),  -- 待分拆的字符串
@split   varchar ( 10 )     -- 数据分隔符
) RETURNS TABLE
AS
RETURN (
    SELECT col = CAST ( SUBSTRING ( @s ,ID, CHARINDEX ( @split , @s + @split ,ID) - ID) as varchar ( 100 ))
    FROM tb_splitSTR
    WHERE ID <= LEN ( @s + ' a ' )
        AND CHARINDEX ( @split , @split + @s ,ID) = ID)
GO


/* ============================================== */

if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[f_splitSTR] ' ) and xtype in (N ' FN ' , N ' IF ' , N ' TF ' ))
drop function [ dbo ] . [ f_splitSTR ]
GO

-- 3.2.5 将数据项按数字与非数字再次拆份
CREATE FUNCTION f_splitSTR(
@s    varchar ( 8000 ),    -- 待分拆的字符串
@split varchar ( 10 )     -- 数据分隔符
) RETURNS @re TABLE (No varchar ( 100 ),Value varchar ( 20 ))
AS
BEGIN
    -- 创建分拆处理的辅助表(用户定义函数中只能操作表变量)
    DECLARE @t TABLE (ID int IDENTITY ,b bit )
    INSERT @t (b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b

    INSERT @re
    SELECT     No = REVERSE ( STUFF (col, 1 , PATINDEX ( ' %[^-^.^0-9]% ' ,col + ' a ' ) - 1 , '' )),
        Value = REVERSE ( LEFT (col, PATINDEX ( ' %[^-^.^0-9]% ' ,col + ' a ' ) - 1 ))
    FROM (
        SELECT col = REVERSE ( SUBSTRING ( @s ,ID, CHARINDEX ( @split , @s + @split ,ID) - ID))
        FROM @t
        WHERE ID <= LEN ( @s + ' a ' )
            AND CHARINDEX ( @split , @split + @s ,ID) = ID)a
    RETURN
END
GO


/* ============================================== */

if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[f_splitSTR] ' ) and xtype in (N ' FN ' , N ' IF ' , N ' TF ' ))
drop function [ dbo ] . [ f_splitSTR ]
GO

-- 3.2.6 分拆短信数据
CREATE FUNCTION f_splitSTR( @s varchar ( 8000 ))
RETURNS @re TABLE (split varchar ( 10 ),value varchar ( 100 ))
AS
BEGIN
    DECLARE @splits TABLE (split varchar ( 10 ),splitlen as LEN (split))
    INSERT @splits (split)
    SELECT ' AC ' UNION ALL
    SELECT ' BC ' UNION ALL
    SELECT ' CC ' UNION ALL
    SELECT ' DC '    
    DECLARE @pos1 int , @pos2 int , @split varchar ( 10 ), @splitlen int
    SELECT TOP 1
        @pos1 = 1 , @split = split, @splitlen = splitlen
    FROM @splits
    WHERE @s LIKE split + ' % '
    WHILE @pos1 > 0
    BEGIN
        SELECT TOP 1
            @pos2 = CHARINDEX (split, @s , @splitlen + 1 )
        FROM @splits
        WHERE CHARINDEX (split, @s , @splitlen + 1 ) > 0
        ORDER BY CHARINDEX (split, @s , @splitlen + 1 )
        IF @@ROWCOUNT = 0
        BEGIN
            INSERT @re VALUES ( @split , STUFF ( @s , 1 , @splitlen , '' ))
            RETURN
        END
        ELSE
        BEGIN
            INSERT @re VALUES ( @split , SUBSTRING ( @s , @splitlen + 1 , @pos2 - @splitlen - 1 ))
            SELECT TOP 1
                @pos1 = 1 , @split = split, @splitlen = splitlen, @s = STUFF ( @s , 1 , @pos2 - 1 , '' )
            FROM @splits
            WHERE STUFF ( @s , 1 , @pos2 - 1 , '' ) LIKE split + ' % '
        END
    END
    RETURN
END
GO