可以創建一个函數来處理
create function dbo.fn_f(@s varchar(1000))
returns varchar(1000)
as
begin
declare @a varchar(1000),@b varchar(1000)
declare @i int
set @a=''
set @i=1
while @i>0
begin
select @a=@a+CompanyPropertyName+';' from tab1 where CompanyPropertyID=substring(@s,@i,2)
set @i=charindex(';',@s,@i+3)-2
end
return(left(@a,len(@a)-1))
end
create function dbo.fn_f(@s varchar(1000))
returns varchar(1000)
as
begin
declare @a varchar(1000),@b varchar(1000)
declare @i int
set @a=''
set @i=1
while @i>0
begin
select @a=@a+CompanyPropertyName+';' from tab1 where CompanyPropertyID=substring(@s,@i,2)
set @i=charindex(';',@s,@i+3)-2
end
return(left(@a,len(@a)-1))
end
解决方案 »
- SQLSERVER镜像切换
- 关于SQL Sever2000 主键和外键的关联
- 这种SQL请问怎么写
- 高分求教一个三表联合查询语句。
- 安装数据库的问题
- 请问 时间减时间 得到天数,是怎么弄的呢?
- SQL Server 2000 Driver for JDBC Service Pack 3 能连接sql server 97吗?
- 为什么查询分析器里面提交查询不超时,在VB中提交查询会超时?已设置了查询时间为无限.
- win7 64位安装sql2005的问题
- select distinct * into #temp from ziliao出错?错在什么地方?错误提示是。。。
- 打扰一下 想请教你个问题 sql里面有个字段pay_memo是存放ip地址的如221.199.137.218!001 我想请教一个select语句 只选出221.199.137.218
- 为什么我们的SQLServer连不上?
Insert into ta
select '1','aaa'
union all select '2','bbb'
union all select '3','ccc'create table tb (id int, value varchar(20))
Insert into tb
select '1','aaa,bbb,'
union all select '2','bbb,ccc,'
union all select '3','aaa,bbb,ccc,'select * from ta
select * from tb--函數
create function dbo.fn_m(@vchstring varchar(1000))
returns varchar(1000)
as
begin
declare @intstart int,@intlocation int
declare @s varchar(1000),@vchsubstring varchar(100)
select @intstart =1,@s=''
select @intlocation = charindex(',',@vchstring,@intstart)
while (@intlocation <>0 )
begin
select @vchsubstring=substring(@vchstring,@intstart,@intlocation-@intstart)
select @s=@s+cast([id] as varchar)+',' from ta where value=@vchsubstring
select @intstart = @intlocation +1
select @intlocation = charindex(',',@vchstring,@intstart)
end
return(@s)
end--刪除
drop table ta
drop table tb
drop function dbo.fn_mselect id, value=dbo.fn_m(value) from tb
--結果
id value
------------------
1 1,2,
2 2,3,
3 1,2,3,
CREATE function dbo.fn_m(@vchstring varchar(1000))
returns varchar(1000)
as
begin
declare @reutrunStr varchar(1000)
declare @id int
declare @value varchar(100)
set @reutrunStr=@vchstring
declare fn_cursor cursor local for select id,value from ta
open fn_cursor
fetch next from fn_cursor into @ID,@value
set @value=ltrim(rtrim(@value))
while @@FETCH_STATUS=0
begin
set @reutrunStr=replace(@reutrunStr,@value,@id) fetch next from fn_cursor into @ID,@value
set @value=ltrim(rtrim(@value))
end
close fn_cursor
deallocate fn_cursor
return(@reutrunStr)
end
执行下一条语句就可以了select id, value=dbo.fn_m(value) from tb