关于replace替换的问题
程序一
declare @begin as nvarchar(1500),@end as nvarchar(1500),@a as nvarchar(150) ,@b as nvarchar(150)
declare @i as int,@count as int, @m as int,@j as int,@k as int
set @i=(select min(a.id) from ( select Begindate,Enddate,id
from TBL_Hk_co_perprofile
where len(Begindate)>10 and len(enddate)>10) a )--从查询结果中查
select @count=(select max(a.id) from ( select Begindate,Enddate,id from TBL_Hk_co_perprofile where len(Begindate)>10 and len(enddate)>10) a )
while @i<=@count
begin
select @begin=replace((select begindate from( select Begindate,Enddate,id from TBL_Hk_co_perprofile where len(Begindate)>10 and len(enddate)>10) a where id =@i)+',','N/A','0000-00-00')
select @end=replace(( select Enddate from ( select Begindate,Enddate,id from TBL_Hk_co_perprofile where len(Begindate)>10 and len(enddate)>10) a where [ID]=@i )+',','N/A','9999-00-00')
set @m =LEN(@begin)
while @m>0
begin
set @j=patindex('%,%',@begin)
set @k=patindex('%,%',@end)
set @a=substring(@begin,1,@j-1)--截取下一个逗号前的字符
set @b=substring(@end,1,@k-1)
if @a>@b
begin
select id,begindate,Enddate from TBL_Hk_co_perprofile where id=@i
end
select @begin=substring(@begin,patindex('%,%',@begin)+1,len(@begin)-patindex('%,%',@begin))
select @end=substring(@end,patindex('%,%',@end)+1,len(@end)-patindex('%,%',@end))
select @m=LEN(@begin)
end
select @i=(select a.id
from ( select Begindate,Enddate,id from TBL_Hk_co_perprofile where len(Begindate)>10 and len(enddate)>10) a
where a.id in (select top 1 a.id
from ( select Begindate,Enddate,id from TBL_Hk_co_perprofile where len(Begindate)>10 and len(enddate)>10) a
where id>@i
order by id ))--查询下一条id
end
go
程序二
declare @begin as nvarchar(1500),@end as nvarchar(1500),@a as nvarchar(150) ,@b as nvarchar(150)
declare @i as int,@count as int, @m as int,@j as int,@k as int
set @i=(select min(a.id) from ( select Begindate,Enddate,id
from TBL_Hk_co_perprofile
where len(Begindate)>10 and len(enddate)>10) a )--从查询结果中查
select @count=(select max(a.id) from ( select Begindate,Enddate,id from TBL_Hk_co_perprofile where len(Begindate)>10 and len(enddate)>10) a )
while @i<=@count
begin
select @begin=(select begindate from( select Begindate,Enddate,id from TBL_Hk_co_perprofile where len(Begindate)>10 and len(enddate)>10) a where id =@i)+','
select @end=( select Enddate from ( select Begindate,Enddate,id from TBL_Hk_co_perprofile where len(Begindate)>10 and len(enddate)>10) a where [ID]=@i )+','
set @m =LEN(@begin)
while @m>0
begin
set @j=patindex('%,%',@begin)
set @k=patindex('%,%',@end)
set @a=substring(@begin,1,@j-1)--截取下一个逗号前的字符
set @b=substring(@end,1,@k-1)
if @a>@b
begin
select id,begindate,Enddate from TBL_Hk_co_perprofile where id=@i
end
select @begin=substring(@begin,patindex('%,%',@begin)+1,len(@begin)-patindex('%,%',@begin))
select @end=substring(@end,patindex('%,%',@end)+1,len(@end)-patindex('%,%',@end))
select @m=LEN(@begin)
end
select @i=(select a.id
from ( select Begindate,Enddate,id from TBL_Hk_co_perprofile where len(Begindate)>10 and len(enddate)>10) a
where a.id in (select top 1 a.id
from ( select Begindate,Enddate,id from TBL_Hk_co_perprofile where len(Begindate)>10 and len(enddate)>10) a
where id>@i
order by id ))--查询下一条id
end
go
红色部分，为什么程序一做替换后不出结果，程序二是正常运行的 但是有N/A，我想用相应的字符替换，求大神指导
[最优解释]
select @begin=replace('(select begindate from( select Begindate,Enddate,id from TBL_Hk_co_perprofile where len(Begindate)>10 and len(enddate)>10) a where id ='''+@i+''')'+',','N/A','0000-00-00')
select @end=replace('( select Enddate from ( select Begindate,Enddate,id from TBL_Hk_co_perprofile where len(Begindate)>10 and len(enddate)>10) a where [ID]='''+@i+''')'+',','N/A','9999-00-00')