- ---1字符串拆分
- /*
- 有字符串1,23,a
- 要求按逗号拆分为表
- 1
- 23
- a
- */
- --(1)第一种方法(循环截取法)
- create function FC_SlpitStr(@Str nvarchar(4000),@Split nvarchar(100))
- returns @R table (Col nvarchar(100))
- as
- begin
- declare @StrLen int
- set @StrLen=len(@Str)
- while charindex(@Split,@StrLen)>0
- begin
- insert into @R values(left(@Str,charindex(@Split,@StrLen)-1))
- set @Str=stuff(@Str,1,charindex(@Split,@StrLen),'')
- end
- insert into @R values(@Str)
- return
- end
- --print dbo.FC_SlpitStr('1,23,a',',')
- --[database_name.]owner_name.function_name ([argument_expr][,...])
- declare @a nvarchar(4000)
- set @a='1,23,a'
- select dbo.FC_SlpitStr(@a,',')
- --(2)动态T-sql语句
- declare @S varchar(100)
- set @s='1,23,a'
- declare @sql varchar(100)
- set @sql='select col='''+replace(@S, ',' , ''' union all select ''')+''''
- print(@sql)
---1字符串拆分
/*
有字符串1,23,a
要求按逗号拆分为表
1
23
a
*/
--(1)第一种方法(循环截取法)
create function FC_SlpitStr(@Str nvarchar(4000),@Split nvarchar(100))
returns @R table (Col nvarchar(100))
as
begin
declare @StrLen int
set @StrLen=len(@Str)
while charindex(@Split,@StrLen)>0
begin
insert into @R values(left(@Str,charindex(@Split,@StrLen)-1))
set @Str=stuff(@Str,1,charindex(@Split,@StrLen),'')
end
insert into @R values(@Str)
return
end
declare @a nvarchar(4000)
set @a='1,23,a'
select dbo.FC_SlpitStr(@a,',')
--(2)动态T-sql语句
declare @S varchar(100)
set @s='1,23,a'
declare @sql varchar(100)
set @sql='select col='''+replace(@S, ',' , ''' union all select ''')+''''
print(@sql)