分割再成批插入
declare @s varchar( 8000 ),@sql nvarchar( 4000 )
set @s = ' 1,12,1212,4545 '
set @sql = ' insert into t(col) select ' + replace(@s, ' , ' , ' col union all select ' )
exec(@sql)
测试
drop table #table
declare @s varchar( 8000 ),@sql nvarchar( 4000 )
set @s = ' 1,12,1212,4545,454 '
create table #table (col int )
set @sql = ' insert into #table(col) select ' + replace(@s, ' , ' , ' col union all select ' )
exec(@sql)
select * from #table
create function f_split(@SourceSql varchar( 8000 ),@StrSeprate varchar( 10 ))
returns @temp table(a varchar( 100 ))
-- 实现split功能 的函数
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
用法:select * from dbo.f_split( ' ABC:BC:C:D:E ' , ' : ' )
、Get_StrArrayLength
CREATE function Get_StrArrayLength
(
@str varchar( 1024 ), -- 要分割的字符串
@split varchar( 10 ) -- 分隔符号
)
returns int
as
begin
declare @location int
declare @start int
declare @length int
set @str = ltrim(rtrim(@str))
set @location = charindex(@split,@str)
set @length = 1
while @location <> 0
begin
set @start = @location + 1
set @location = charindex(@split,@str,@start)
set @length = @length + 1
end
return @length
end
2 、Get_StrArrayStrOfIndex
CREATE function Get_StrArrayStrOfIndex
(
@str varchar( 1024 ), -- 要分割的字符串
@split varchar( 10 ), -- 分隔符号
@index int -- 取第几个元素
)
returns varchar( 1024 )
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int
set @str = ltrim(rtrim(@str))
set @start = 1
set @next = 1
set @seed = len(@split)
set @location = charindex(@split,@str)
while @location <> 0 and @index > @next
begin
set @start = @location + @seed
set @location = charindex(@split,@str,@start)
set @next = @next + 1
end
if @location = 0 select @location = len(@str) + 1
-- 这儿存在两种情况: 1 、字符串不存在分隔符号 2 、字符串中存在分隔符号,跳出while循环后,@location为0,那默认为字符串后边有一个分隔符号。
return substring(@str,@start,@location - @start)
end
3 、应用:
set @next = 1
while @next <= dbo.Get_StrArrayLength(@KeyWords, ' , ' )
begin
if dbo.Get_StrArrayStrOfIndex(@KeyWords, ' , ' ,@next) <> ''
set @strwhere = @strwhere + ' or Keywords like '' % ' + dbo.Get_StrArrayStrOfIndex(@KeyWords, ' , ' ,@next) + ' % '''
set @next = @next + 1
end
declare @s varchar( 8000 ),@sql nvarchar( 4000 )
set @s = ' 1,12,1212,4545 '
set @sql = ' insert into t(col) select ' + replace(@s, ' , ' , ' col union all select ' )
exec(@sql)
测试
drop table #table
declare @s varchar( 8000 ),@sql nvarchar( 4000 )
set @s = ' 1,12,1212,4545,454 '
create table #table (col int )
set @sql = ' insert into #table(col) select ' + replace(@s, ' , ' , ' col union all select ' )
exec(@sql)
select * from #table
create function f_split(@SourceSql varchar( 8000 ),@StrSeprate varchar( 10 ))
returns @temp table(a varchar( 100 ))
-- 实现split功能 的函数
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
用法:select * from dbo.f_split( ' ABC:BC:C:D:E ' , ' : ' )
、Get_StrArrayLength
CREATE function Get_StrArrayLength
(
@str varchar( 1024 ), -- 要分割的字符串
@split varchar( 10 ) -- 分隔符号
)
returns int
as
begin
declare @location int
declare @start int
declare @length int
set @str = ltrim(rtrim(@str))
set @location = charindex(@split,@str)
set @length = 1
while @location <> 0
begin
set @start = @location + 1
set @location = charindex(@split,@str,@start)
set @length = @length + 1
end
return @length
end
2 、Get_StrArrayStrOfIndex
CREATE function Get_StrArrayStrOfIndex
(
@str varchar( 1024 ), -- 要分割的字符串
@split varchar( 10 ), -- 分隔符号
@index int -- 取第几个元素
)
returns varchar( 1024 )
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int
set @str = ltrim(rtrim(@str))
set @start = 1
set @next = 1
set @seed = len(@split)
set @location = charindex(@split,@str)
while @location <> 0 and @index > @next
begin
set @start = @location + @seed
set @location = charindex(@split,@str,@start)
set @next = @next + 1
end
if @location = 0 select @location = len(@str) + 1
-- 这儿存在两种情况: 1 、字符串不存在分隔符号 2 、字符串中存在分隔符号,跳出while循环后,@location为0,那默认为字符串后边有一个分隔符号。
return substring(@str,@start,@location - @start)
end
3 、应用:
set @next = 1
while @next <= dbo.Get_StrArrayLength(@KeyWords, ' , ' )
begin
if dbo.Get_StrArrayStrOfIndex(@KeyWords, ' , ' ,@next) <> ''
set @strwhere = @strwhere + ' or Keywords like '' % ' + dbo.Get_StrArrayStrOfIndex(@KeyWords, ' , ' ,@next) + ' % '''
set @next = @next + 1
end