博客

分隔字符串的function

ALTER function [dbo].[SplitString]
(
    @Input nvarchar(max),
    @Separator nvarchar(max)=',', 
    @RemoveEmptyEntries bit=1 
)
returns @TABLE table 
(
    [Id] int identity(1,1),
    [Value] nvarchar(max)
) 
as
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 @TABLE([Value]) Values(@Entry)
            end
--删除字符串中的第一个截取的字符
        set @Input = substring(@Input, @Index+datalength(@Separator)/2, len(@Input))
--重新设置index的位置
        set @Index = charindex(@Separator, @Input)
    end
--最后一个字符，去掉左右空格
    set @Entry=ltrim(rtrim(@Input))
    if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')
        begin
            insert into @TABLE([Value]) Values(@Entry)
        end

    return
end

DECLARE @val varchar(5000)
declare @rows int
declare @n int
declare @new int    
select @n=1
IF OBJECT_ID('tempdb..#tmp_emp') IS NOT NULL DROP TABLE #tmp_emp
select * into #tmp_emp from SplitString('3,1,''测试点'',0,''2015/06/2'',''2015/06/2'',''22'';1,1,''xx'',''1010'',''9955'',''xx@163.com'',''test'',1,1,''22''
',';',1)
select @rows= COUNT(0) from #tmp_emp
while @n <= @rows
begin

select @val = [value] from #tmp_emp where id=@n

IF OBJECT_ID('tempdb..#tmp_emp2') IS NOT NULL DROP TABLE #tmp_emp2
select * into #tmp_emp2 from SplitString(@val,',',1)
select * from #tmp_emp2

select @new = COUNT(0) from HB_M_Store where StoreCD = (select [VALUE] from #tmp_emp2 where id=1)

if(@new <> 0)
BEGIN

select * from HB_M_Store

insert into HB_M_Store values(3,1,'测试点',(select [VALUE] from #tmp_emp2 where id=1),'2015/06/2','2015/06/2','10108701');



END

--INSERT into HB_M_Store VALUES();



select @n=@n+1
end