分割再成批插入
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
数据库分割再成批插入
最新推荐文章于 2022-07-22 15:39:10 发布