關于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