0020 sql 使用大全（3）

20：各种字符串分拆处理函数

21：各种字符串合并处理函数




20：---------------------------------各种字符串分拆处理函数---------------------------------------------




--各种字符串分函数

 

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

21：---------------------------------------------各种字符串合并处理函数--------------------------------------------------------

--各种字符串分函数

 

--3.3.1 使用游标法进行字符串合并处理的示例。

--处理的数据

CREATE TABLE tb(col1 varchar(10),col2 int)

INSERT tb SELECT 'a',1

UNION ALL SELECT 'a',2

UNION ALL SELECT 'b',1

UNION ALL SELECT 'b',2

UNION ALL SELECT 'b',3

 

--合并处理

--定义结果集表变量

DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))

 

--定义游标并进行合并处理

DECLARE tb CURSOR LOCAL

FOR

SELECT col1,col2 FROM tb ORDER BY  col1,col2

DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)

OPEN tb

FETCH tb INTO @col1,@col2

SELECT @col1_old=@col1,@s=''

WHILE @@FETCH_STATUS=0

BEGIN

    IF @col1=@col1_old

        SELECT @s=@s+','+CAST(@col2 as varchar)

    ELSE

    BEGIN

        INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))

        SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1

    END

    FETCH tb INTO @col1,@col2

END

INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))

CLOSE tb

DEALLOCATE tb

--显示结果并删除测试数据

SELECT * FROM @t

DROP TABLE tb

/*--结果

col1       col2

---------- -----------

a          1,2

b          1,2,3

--*/

GO

 

 

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

 

 

--3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例

--处理的数据

CREATE TABLE tb(col1 varchar(10),col2 int)

INSERT tb SELECT 'a',1

UNION ALL SELECT 'a',2

UNION ALL SELECT 'b',1

UNION ALL SELECT 'b',2

UNION ALL SELECT 'b',3

GO

 

--合并处理函数

CREATE FUNCTION dbo.f_str(@col1 varchar(10))

RETURNS varchar(100)

AS

BEGIN

    DECLARE @re varchar(100)

    SET @re=''

    SELECT @re=@re+','+CAST(col2 as varchar)

    FROM tb

    WHERE col1=@col1

    RETURN(STUFF(@re,1,1,''))

END

GO

 

--调用函数

SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1

--删除测试

DROP TABLE tb

DROP FUNCTION f_str

/*--结果

col1       col2

---------- -----------

a          1,2

b          1,2,3

--*/

GO

 

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

 

 

--3.3.3 使用临时表实现字符串合并处理的示例

--处理的数据

CREATE TABLE tb(col1 varchar(10),col2 int)

INSERT tb SELECT 'a',1

UNION ALL SELECT 'a',2

UNION ALL SELECT 'b',1

UNION ALL SELECT 'b',2

UNION ALL SELECT 'b',3

 

--合并处理

SELECT col1,col2=CAST(col2 as varchar(100)) 

INTO #t FROM tb

ORDER BY col1,col2

DECLARE @col1 varchar(10),@col2 varchar(100)

UPDATE #t SET 

    @col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END,

    @col1=col1,

    col2=@col2

SELECT * FROM #t

/*--更新处理后的临时表

col1       col2

---------- -------------

a          1

a          1,2

b          1

b          1,2

b          1,2,3

--*/

--得到最终结果

SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1

/*--结果

col1       col2

---------- -----------

a          1,2

b          1,2,3

--*/

--删除测试

DROP TABLE tb,#t

GO

 

 

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

 

--3.3.4.1 每组 <=2 条记录的合并

--处理的数据

CREATE TABLE tb(col1 varchar(10),col2 int)

INSERT tb SELECT 'a',1

UNION ALL SELECT 'a',2

UNION ALL SELECT 'b',1

UNION ALL SELECT 'b',2

UNION ALL SELECT 'c',3

 

--合并处理

SELECT col1,

    col2=CAST(MIN(col2) as varchar)

        +CASE 

            WHEN COUNT(*)=1 THEN ''

            ELSE ','+CAST(MAX(col2) as varchar)

        END

FROM tb

GROUP BY col1

DROP TABLE tb

/*--结果

col1       col2      

---------- ----------

a          1,2

b          1,2

c          3

--*/

 

--3.3.4.2 每组 <=3 条记录的合并

--处理的数据

CREATE TABLE tb(col1 varchar(10),col2 int)

INSERT tb SELECT 'a',1

UNION ALL SELECT 'a',2

UNION ALL SELECT 'b',1

UNION ALL SELECT 'b',2

UNION ALL SELECT 'b',3

UNION ALL SELECT 'c',3

 

--合并处理

SELECT col1,

    col2=CAST(MIN(col2) as varchar)

        +CASE 

            WHEN COUNT(*)=3 THEN ','

                +CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN(MAX(a.col2),MIN(a.col2))) as varchar)

            ELSE ''

        END

        +CASE 

            WHEN COUNT(*)>=2 THEN ','+CAST(MAX(col2) as varchar)

            ELSE ''

        END

FROM tb a

GROUP BY col1

DROP TABLE tb

/*--结果

col1       col2

---------- ------------

a          1,2

b          1,2,3

c          3

--*/

GO