【SOS】求一Sql修改语句

[huangwenquan123]

表Company_Tbl其中有两个字段CompPhotos,CompLinks
存进去的形式是以|分割开的
例如：
CompPhotos:/Upload/CompanyTemplete/flashing/38/38_1.jpg|/Upload/CompanyTemplete/flashing/38/38_3.jpg|
CompLinks:www.baidu.com|www.youku.com|
现在要修改里面的值
例如传入一个索引
index=1;
那就删除CompPhotos,Complinks第一个|前面的值
index=2
那就删除CompPhotos,Complinks第二个|前面的值

该怎么写sql语句呢？
求高手帮忙

提供sql测试语句

 L
create table ceshi(CompPhotos varchar(255),CompLinks varchar(255))
insert into ceshi(CompPhotos,CompLinks) 
values('/Upload/CompanyTemplete/flashing/38/38_1.jpg|/Upload/CompanyTemplete/flashing/38/38_3.jpg|',
'www.baidu.com|www.youku.com|')

[huangwenquan123]

自己顶一下 江湖救急！

[快溜]

 
create function getstr(@str varchar(1000),@index int)
returns varchar(max)
as
begin
     declare @str1 varchar(500),@no int
     set @str1=''
     set @no=1
     while 1=1
     begin
         if(@no<@index)
         begin
           set @str1=@str1+left(@str,charindex('|',@str))
           set @str=right(@str,len(@str)-charindex('|',@str))
         end
         else
          begin
               set @str=right(@str,len(@str)-charindex('|',@str))
               break
          end
         set @no=@no+1
        
     end
    return @str1+@str
end

create table ceshi(CompPhotos varchar(255),CompLinks varchar(255))
insert into ceshi(CompPhotos,CompLinks) 
values('/Upload/CompanyTemplete/flashing/38/38_1.jpg|/Upload/CompanyTemplete/flashing/38/38_3.jpg|',
'www.baidu.com|www.youku.com|')


select dbo.getstr(CompPhotos,1),dbo.getstr(CompLinks,2) from ceshi

/*                                                                   
---------------------------------------------------- -----------------
/Upload/CompanyTemplete/flashing/38/38_3.jpg|         www.baidu.com|

[duoxu1983]

[code=SQ]L
--L
declare @index int
set @index=2
if @index=1
begin
select replace(CompPhotos,substring(CompPhotos,1,charindex('|',CompPhotos)),''),replace(CompLinks,substring(CompLinks,1,charindex('|',CompLinks)),'') from ceshi
end
if @index=2
begin
select replace(CompPhotos,substring(CompPhotos,charindex('|',CompPhotos),len(CompPhotos)),''),replace(CompLinks,substring(CompLinks,charindex('|',CompLinks),len(CompLinks)),'') from ceshi
end
[/code]

[duoxu1983]

L
declare @index int
set @index=2
if @index=1
begin
	select replace(CompPhotos,substring(CompPhotos,1,charindex('|',CompPhotos)),''),replace(CompLinks,substring(CompLinks,1,charindex('|',CompLinks)),'') from ceshi
end
if @index=2
begin
	select replace(CompPhotos,substring(CompPhotos,charindex('|',CompPhotos),len(CompPhotos)),''),replace(CompLinks,substring(CompLinks,charindex('|',CompLinks),len(CompLinks)),'') from ceshi
end

[AcHerat]

--
create table ceshi(CompPhotos varchar(255),CompLinks varchar(255))
insert into ceshi(CompPhotos,CompLinks) 
values('/Upload/CompanyTemplete/flashing/38/38_1.jpg|/Upload/CompanyTemplete/flashing/38/38_3.jpg|',
'www.baidu.com|www.youku.com|')
go

create function getstr(@a varchar(1000),@b varchar(1000),@index int)
returns varchar(max)
as
begin
     declare @str1 varchar(500),@str varchar(500),@no int
	 set @str = @a + '^' + @b
     set @str1=''
     set @no=1
     while 1=1
     begin
         if(@no<@index)
         begin
           set @str1=@str1+left(@str,charindex('|',@str))
           set @str=right(@str,len(@str)-charindex('|',@str))
         end
         else
          begin
               set @str=right(@str,len(@str)-charindex('|',@str))
               break
          end
         set @no=@no+1
        
     end
    return @str1+@str
end
go

declare @i int
set @i = 1

;with t as
(
	select dbo.getstr(CompPhotos,CompLinks,@i)st
	from ceshi
)

select substring(st,1,charindex('^',st) - 1)CompPhotos,
	   substring(st,charindex('^',st) + 1,len(st) - charindex('^',st))CompLinks
from t

drop function getstr
drop table ceshi




CompPhotos                                                                                                                                                                                                                                                       CompLinks
---------------------------------------------------------------------------------------------------
/Upload/CompanyTemplete/flashing/38/38_3.jpg|                                                      www.baidu.com|www.youku.com|

(1 行受影响)

[AcHerat]

--
CompPhotos                                                                                      CompLinks
---------------------------------------------------------------------------------------------------------------/Upload/CompanyTemplete/flashing/38/38_3.jpg|                                                          www.baidu.com|www.youku.com|

(1 行受影响)