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

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

--字符串拆分成行
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 @userid, 35, @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

转载：http://www.cnblogs.com/zengxiangzhan/archive/2010/01/03/1638171.html