split函数的修改，通过存储过程实现整表所有列的拆分

/*
一、首先创建数据库
*/
if exists(select 1 from sys.sysdatabases where name='web_1')
begin
  drop database web_1
end

create database web_1  
on PRIMARY
(  
    NAME = web,  
    FILENAME='E:\test\web.mdf',   --此路径必须存在才能建成功
    SIZE = 10,  
    MAXSIZE = UNLIMITED,  
    FILEGROWTH = 5  
)  
LOG ON
(  
    NAME='web_dat',  
    FILENAME='E:\test\web.ldf',  --此路径必须存在才能建成功
    SIZE =5MB,  
    MAXSIZE = 25MB,  
    FILEGROWTH =5MB  
)  
GO
/*
二、创建需要拆分数据的测试表
*/
Use web_1

if exists(select 1 from sys.sysobjects where name ='test') --查看表是否存在
begin
 drop table test
end

create table test
(
id int identity(1,1),
name varchar(255)
)
alter table test add constraint PK_test_id primary key(id)

insert into test(name) values('aa,;bb,;cc,;dd')
insert into test(name) values('11,;22,;33,;444')

select * from test

/*
三、创建类型
*/
if exists(SELECT 1 FROM sys.types WHERE name = 'LocationTableType') --确认某type是否存在
begin
    drop  TYPE LocationTableType
end

CREATE TYPE LocationTableType AS TABLE
(
    Input nvarchar(max),    
    Separator nvarchar(max) default ',',
    RemoveEmptyEntries bit  default 1
);
/*
四、创建存储过程
*/
if exists(select 1 from sys.sysobjects   where xtype ='P' and name='SplitString')
begin
   drop procedure  [SplitString]
end

 


create  Procedure [dbo].[SplitString]  
(@TVP dbo.LocationTableType readonly) --传入表
as
begin
   declare @OrignalInput nvarchar(max)
   declare @Input nvarchar(max)   
   declare @Separator nvarchar(max)
   declare @RemoveEmptyEntries bit
   
   declare  @rev  table
   (
    OrignalInput nvarchar(max),
    DealName nvarchar(max)
   ) --定义一个表的类型，作为返回表
   
   declare Split_Cursor Cursor for
   select Input,Input,Separator,RemoveEmptyEntries from  @TVP --使用游标实现数据循环拆分
   
   open Split_Cursor
   fetch next from Split_Cursor into @OrignalInput,@Input,@Separator,@RemoveEmptyEntries--与上面的查询顺序一致
   while @@FETCH_STATUS=0
   begin
       --实际处理字符串
        declare @Index int, @Entry nvarchar(max)
        set @Index = charindex(@Separator,@Input)
        while (@Index>0)
        begin
            set @Entry=ltrim(rtrim(substring(@Input, 1, @Index-1)))
            
            if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')
                begin
                    insert into @rev(OrignalInput,DealName)Values(@OrignalInput,@Entry)
                end

            set @Input = substring(@Input, @Index+datalength(@Separator)/2, len(@Input))
            set @Index = charindex(@Separator, @Input)
        end
        
        set @Entry=ltrim(rtrim(@Input))
        if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')
            begin
                insert into @rev(OrignalInput,DealName)Values(@OrignalInput,@Entry)
            end
        
        fetch next from Split_Cursor into @OrignalInput,@Input,@Separator,@RemoveEmptyEntries
    end
    select * from @rev  --显示结果
    close Split_Cursor --关闭游标
    deallocate Split_Cursor    --释放资源
end
 
/*
五、调用存储过程实现拆分
*/

DECLARE @LocationTVP  AS LocationTableType;

INSERT INTO @LocationTVP (Input, Separator,RemoveEmptyEntries)
select name,',;',1 from test --将实表的数据插入到类型中

EXEC [SplitString] @LocationTVP

/*
六、说明

此处的返回表不是实体表，若是不止需要拆分列的内容，可以将表改为实体表，里面有个拆分前的数据列，可以用来与原表关联
*/

/*
七、删除测试
*/

drop procedure SplitString

drop  TYPE LocationTableType

drop table test