想查找并删除某字段内的重复字符，这个SQL如何写？

[KOON]

如题
假如一个字段XXX，中有记录TEST;START;TEST，我想要删除掉多余的那个TEST，该如何写SQL

[welyngj]

UPDATE table
SET XXX='TEST;START;'
WHERE XXX='TEST;START;TEST'

[hglhyy]

楼上的不行吧，帮你ＵＰ！

[ivy_live521]

http://expert.csdn.net/Expert/topic/2554/2554057.xml?temp=.2432062

[KOON]

楼上误解了，我的意思是我的字段中每条记录中可能有重复的，可能没有重复的，记录的内容是用";"来分开的，若是发现一条记录中有两个以上的重复内容，删除多余的内容，
ivy_live521(妞妞)，wunike(求知者)谢谢，不过好像我这里不适用

[devilwind]

create table a1(name varchar(10))
insert a1
select 'test;ssf;test;ddd;df;test'
union select 'dd'
union select 'ss'
union select 'test'
union select 'tessss'


（所影响的行数为 5 行）
dd
ss
tessss
test
test;ssf;test;ddd;df;test






create proc testtt
@name varchar(100)
as
if object_id('tempdb..#testtt') is not null
drop table #testtt
create table #testtt(name varchar(20))
declare @t varchar(20),@t2 varchar(100),@t3 varchar(100)
select @t3=@name
while charindex(';',@name)>0
begin
select @t=substring( @name,1,charindex(';',@name)-1)
if not exists(select 1 from #testtt where name=@t)
insert #testtt select @t
select @name=substring( @name,charindex(';',@name)+1,len(@name)-charindex(';',@name))
end
select @t2=''
select @t2=@t2+name+';' from #testtt
select @t2=substring(@t2,1,len(@t2)-1)
update a1 set name=@t2 where name=@t3


（所影响的行数为 5 行）
dd
ss
tessss
test
test;ssf;ddd;df

[azsoft]

declare @ varchar(8000)
set @=''
select @=@+rtrim(name)+' from t1 union all select ' from syscolumns where id=object_id('t1')
set @=left(@,len(@)-len(' from t1 union all select '))
--print @
exec('select '+@+' from t1')
行转列
Delete from ...
列转行

declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+' from test group by name'
exec(@sql)

[yy007]

delete xxx where id in(select id from xxx a where a.id>(select min(b.id) from xxx b where a.test=b.test))就行了！

前提是你要在你的xxx表里在建一个id字段，定义为递增变量就ok了！！！

[inkstone]

declare @chvTest varchar(800)
select @chvTest='Test;test;xixi;haha;test'
create table #test
(
chvString varchar(20),
intID int IDENTITY (1, 1)
)

declare @chvTempString varchar(20),@intCharIndex int

while len(@chvTest)<>0
begin
select @chvTest = ltrim(rtrim(@chvTest))
select @intCharIndex = charindex(';',@chvTest,1)
if @intCharIndex<>0
select @chvTempString = left(@chvTest,@intCharIndex-1)
else
select @chvTempString = @chvTest
if not exists(select 1 from #test where chvString=@chvTempString)
insert into #test values(@chvTempString)
if len(@chvTempString) <> len(@chvTest)
select @chvTest =right(@chvTest,len(@chvTest)-len(@chvTempString)-1)
else
select @chvTest=''
end
declare @ReturnString varchar(800)
select @ReturnString=''
declare @subString varchar(20)
declare cur_string cursor for
select chvString from #test
open cur_string
fetch next from cur_string into @subString
while @@fetch_status=0
begin
select @ReturnString=@ReturnString+@subString + ';'
fetch next from cur_string into @subString
end
close cur_string
deallocate cur_string
select @ReturnString=left(@ReturnString,len(@ReturnString)-1)
print @ReturnString
drop table #test

[inkstone]

刚才忘了过滤空格
declare @chvTest varchar(800)
select @chvTest='Test ; test; xixi; haha ; test'
create table #test
(
chvString varchar(20),
intID int IDENTITY (1, 1)
)

declare @chvTempString varchar(20),@intCharIndex int

while len(@chvTest)<>0
begin
select @chvTest = ltrim(rtrim(@chvTest))
--print @chvTest
select @intCharIndex = charindex(';',@chvTest,1)
if @intCharIndex<>0
select @chvTempString = left(@chvTest,@intCharIndex-1)
else
select @chvTempString = @chvTest
if not exists(select 1 from #test where chvString=@chvTempString) and len(@chvTempString)<>0
insert into #test values(@chvTempString)
if len(@chvTempString) <> len(@chvTest)
select @chvTest =right(@chvTest,len(@chvTest)-len(@chvTempString)-1)
else
select @chvTest=''
end
declare @ReturnString varchar(800)
select @ReturnString=''
declare @subString varchar(20)
declare cur_string cursor for
select ltrim(rtrim(chvString)) from #test
open cur_string
fetch next from cur_string into @subString
while @@fetch_status=0
begin
select @ReturnString=@ReturnString+@subString + ';'
fetch next from cur_string into @subString
end
close cur_string
deallocate cur_string
select @ReturnString=left(@ReturnString,len(@ReturnString)-1)
print @ReturnString
drop table #test

[victorycyz]

按你的题意，不光是test要删掉，“；”、“S”、“T”等字符都是重复的，如果写成代码，恐怕剩下的就会是这个样子了：
TESAR

[victorycyz]

按你的题意，不光是test要删掉，“；”、“S”、“T”等字符都是重复的，如果写成代码，恐怕剩下的就会是这个样子了：
TES;AR

[wunike]

用以下语句试试
select distinct xxx
from 表名