字符串分割函数--拆分成多行

--字符串拆分成行
declare @str varchar(8000
set @str = 'a1,b1,c2,d1,e3,f5' 
--,换成 union all select
set @str = 'select  name='''+replace(@str,',',''' union all select ''')+'''' 
exec(@str)

/*name 
---- 
a1
b1
c2
d1
e3
f5
*/

 

 

--字符串分割函数--拆分成多行
create function f_splitstr(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp 
table(F1 varchar(100))
as
begin 
declare @i int 
set @SourceSql=rtrim(ltrim(@SourceSql)) 
set @i=charindex(@StrSeprate,@SourceSql
while @i>=1 
begin  
insert @temp values(left(@SourceSql,@i-1))  
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)  
set @i=charindex(@StrSeprate,@SourceSql) end if @SourceSql<>''     
insert @temp values(@SourceSql
return end

--示例
declare @SourceSql varchar(8000),@StrSeprate varchar(10)
select @SourceSql='1,2,3,4,5',@StrSeprate=','
select * from dbo.f_splitstr(@SourceSql,@StrSeprate)

 

 


--结果
/*

F1                                                                                               
--
1
2
3
4
5
(所影响的行数为 5 行)
*/

 

 

 

 

 

-- XML  

DECLARE @str VARCHAR(4000)
SET @str= '12,13,14,16,44,46,47'
Declare @x XML 
select @x = cast('<A>'+ replace(@str,',','</A><A>')+ '</A>' as xml)
      
select t.value('.''int'as inVal
from @x.nodes('/A'as x(t)

/*
inVal:
12
13
14
16
44
46
47

*/ 

 

 DECLARE @str VARCHAR(4000)

SET @str= '12,大幅度,14,16,高度,46,47'
Declare @x XML 
select @x = cast('<A>'+ replace(@str,',','</A><A>')+ '</A>' as xml)
      
select t.value('.''VARCHAR(40)'as inVal
from @x.nodes('/A'as x(t)

/*
inVal
----------------------------------------
12
大幅度
14
16
高度
46
47

(7 行受影响)
*/

 

 alter procedure Proc_InsertSendApi(

    @userid       int,
    @sendid       int,
    @Phone        nvarchar(max),
    @message      nvarchar(300),
    @senttime     datetime,
    @batchid      bigint
)
as
begin
    declare @str nvarchar(max)
    set @str = @Phone
    declare @x     xml,
            @p     varchar(11)
    
    select @x = cast('<A>' + replace(@str',''</A><A>'+ '</A>' as xml)     
    
    begin
        set arithabort on
        
        insert into Send_Api
          (
            UserId,
            PartnersCode,
            SendId,
            Phone,
            [Message],
            SentTime,
            CreateTime,
            BatchId
          )
        select @userid35@sendid, t.value('.''varchar(11)'), @message@senttime
               getdate(), @batchid
        from   @x.nodes('/A'as x(t)
    end
end

 

表:
Id    Name    Category
1    哈利波特    奇幻,外文,魔法
2    神雕俠侶    武俠,現代
3    西遊記    奇幻,古文

结果:

1    哈利波特    奇幻
1    哈利波特    外文
1    哈利波特    魔法
2    神雕俠侶    武俠
2    神雕俠侶    現代
3    西遊記    奇幻
3    西遊記    古文

CREATE FUNCTION fnConvertXmlToTable(@ID INT)
RETURNS @Table TABLE(Category NVARCHAR(10))
AS
BEGIN
    
DECLARE @Xml XML;
    
--将逗号 Replace 成</Category><Category>,最前面加入<Category>及最后面再加入</Category>
    SELECT TOP 1 @Xml =CAST('<Category>' + REPLACE(Category , ',''</Category><Category>'+ '</Category>' AS XML)
    
FROM dbo.Books
    
WHERE ID = @ID
    
INSERT INTO @Table(Category)
    
SELECT col.value('.''nvarchar(10)')
    
FROM @Xml.nodes('/Category') Doc(col)
    
RETURN
END



SELECT a.ID, a.Name, b.Category FROM Books a
CROSS APPLY dbo.fnConvertXmlToTable(a.ID) b
ORDER BY a.ID
 

posted @ 2010-01-03 11:10 曾祥展 阅读(...) 评论(...) 编辑 收藏