mssql split

来源：互联网发布：北邮软件工程硕士编辑：程序博客网时间：2024/04/29 20:05
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
declare @str as varchar(8000) 
set @str='1-101,2-201,3-306,4-405,5-501,6-602' 
declare @key as datetime 
set @key ='2009/08/12' 
declare @t table 
( 
Day varchar(10) 
,A varchar(10) 
,B int 
)
insert into @t(A) 
select * from  dbo.f_splitSTR(@str,',')
update @t
set Day=convert(varchar(10),@key,111),
   A=LEFT(a,1)
   ,B=RIGHT(a,3)
 select * from @t 
 /*
 
Day        A          B
---------- ---------- -----------
2009/08/12 1          101
2009/08/12 2          201
2009/08/12 3          306
2009/08/12 4          405
2009/08/12 5          501
2009/08/12 6          602


(6 行受影响)


 */