62,243
社区成员




SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: zhangl
-- Create date: 2008-5-6
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE ProcTest_Proc
@list varchar(1000)
AS
BEGIN
SET NOCOUNT ON
Declare @tablelist varchar(8000)
set @tablelist=N''
--根据id组合tablelist列的所有值
select @tablelist=@tablelist+tablelist+',' from T_CheckTable where id in (select * from dbo.Split_Fun(@list,','))
--定义表变量
Declare @t table(tableid varchar(10))
--将分割后的字符存放的表变量中
insert into @t
select * from dbo.Split_Fun(@tablelist,',')
--去掉重复的,取其中的唯一值,并插入到临时表#temp
select distinct tableid into #temp from @t
--组合字符串
Declare @id varchar(1000)
set @id=N''
select @id=@id+tableid+',' from #temp
set @id=substring(@id,len(@id)) --去掉最后的,
drop table #temp
--显示结果
select @id
END
GO
create table T_checkTable
(
id int identity(1,1),
tableList varchar(50) default('1,')
)
insert into t_checktable values('1,2,5,9,')
insert into t_checktable values('1,3,4,9,')
insert into t_checktable values('1,3,4,8,10')
insert into t_checktable values('1,7,11,')
declare @list varchar(100),@temp varchar(10),@temp2 varchar(10),@tempList varchar(500),@result varchar(5000)
set @result=''
set @list='1,3,4,8,15' --设为传入的值
set @list=case when right(@list,1)=',' then @list else @list+',' end --给末尾非逗号结尾的字符串附加逗号
while charindex(',',@list)>0
begin
set @temp=substring(@list,1,charindex(',',@list)-1)
select @tempList=tableList from t_checkTable where [id]=@temp
set @tempList=case when right(@tempList,1)=',' then @tempList else @tempList+',' end
while charindex(',',@tempList)>0
begin
set @temp2=substring(@tempList,1,charindex(',',@tempList)-1)
set @result=case when len(@temp2)>0 and charindex(@temp2,@result)=0 then @result+@temp2+',' else @result end
set @tempList=substring(@tempList,charindex(',',@tempList)+1,len(@tempList))
end
set @list=substring(@list,charindex(',',@list)+1,len(@list))
end
print @result
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: zhangl
-- Create date: 2008-5-6
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE ProcTest_Proc
@list varchar(1000)
AS
BEGIN
SET NOCOUNT ON
Declare @tablelist varchar(8000)
set @tablelist=N''
--根据id组合tablelist列的所有值
select @tablelist=@tablelist+tablelist+',' from T_CheckTable where id in (select * from dbo.Split_Fun(@list,','))
--定义表变量
Declare @t table(tableid varchar(10))
--将分割后的字符存放的表变量中
insert into @t
select * from dbo.Split_Fun(@tablelist,',')
--去掉重复的,取其中的唯一值
Declare @id varchar(1000)
select @id=@id+distinct(tableid)+',' from @t
--取唯一值
select @id
END
GO
--添加表值函数
-- =============================================
-- Author: zhangl
-- ALTER date: 2008-1-14
-- Description: 实现split功能 的函数
-- Test: select dbo.Split_Fun(regionid,',') from userauthority where userno=36
-- =============================================
create function dbo.Split_Fun(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
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
用临时表作为数组
create function f_split(@c varchar(2000),@split varchar(2))
returns @t table(col varchar(20))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(col) values (@c)
return
end
go
select * from dbo.f_split('dfkd,dfdkdf,dfdkf,dffjk',',')
drop function f_split
col
--------------------
dfkd
dfdkdf
dfdkf
dffjk
(所影响的行数为 4 行)