各种字符串分函数 及 实例

--各种字符串分函数

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

 

 

--测试 1
if object_id('test')is not null drop table test
go
create table test(id  int,text1 nvarchar(20))
if object_id('pclass')is not null drop table pclass
go
create table pclass(id int identity,userid int,classid nvarchar(20),class_order int)
go
insert test
select 1, 'a1,a4,a5,a2' union all
select 2 ,'a,b,c' union all
select 3, '11,2,h'

--字符串分拆处理函数
if object_id('f_splitSTR') is not null drop function f_splitSTR
go
CREATE FUNCTION f_splitSTR(
@s   varchar(8000),   --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(col varchar(100),sort int identity)
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

declare @id int,@text1 nvarchar(20)
declare cur cursor for select * from test
open cur
fetch next from cur into @id,@text1
while @@fetch_status=0
begin
 insert pclass select @id,col,sort from dbo.f_splitSTR(@text1,',')
 fetch next from cur into @id,@text1
end
close cur
deallocate cur

select * from pclass

--结果
/*
id          userid      classid              class_order
----------- ----------- -------------------- -----------
1           1           a1                   1
2           1           a4                   2
3           1           a5                   3
4           1           a2                   4
5           2           a                    1
6           2           b                    2
7           2           c                    3
8           3           11                   1
9           3           2                    2
10          3           h                    3

（所影响的行数为 10 行）

*/

 

 

--分段截取函数(邹建)
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