SX8BW,SX8BW,SX8BW,8BD
SX8BW,SX8BW,81WCD
SX8BW,SX8BW,SX8BW,SX8BW,SX8BW,SX81W,SX81W,SX81D
SX8BW,SX8BW,SX8BW,SX786结果
SX8BW
SX8BW,SXWCD
SX8BW,SX81D
SX8BW,SX786即将同一字段内,以5个字符为单位的重复检查,如果有重复的取一条
各位帮忙啊~
SX8BW,SX8BW,81WCD
SX8BW,SX8BW,SX8BW,SX8BW,SX8BW,SX81W,SX81W,SX81D
SX8BW,SX8BW,SX8BW,SX786结果
SX8BW
SX8BW,SXWCD
SX8BW,SX81D
SX8BW,SX786即将同一字段内,以5个字符为单位的重复检查,如果有重复的取一条
各位帮忙啊~
SX8BW,SX8BW,SX8BW,SX8BD
SX8BW,SX8BW,81WCD
SX8BW,SX8BW,SX8BW,SX8BW,SX8BW,SX81W,SX81W,SX81D
SX8BW,SX8BW,SX8BW,SX786结果
SX8BW,SX8BD
SX8BW,SX81WCD
SX8BW,SX81W,SX81D
SX8BW,SX786即将同一字段内,以5个字符为单位的重复检查,如果有重复的取一条
各位帮忙啊~
/*
功能:实现split功能的函数
*/create function fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as begin
declare @i int set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr) while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1)) set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end if @inputstr <> '\'
insert @temp values(@inputstr) return
end
SX8BW,SX8BW,81WCD
SX8BW,SX8BW,SX8BW,SX8BW,SX8BW,SX81W,SX81W,SX81D
SX8BW,SX8BW,SX8BW,SX786结果
SX8BW,SX8BD
SX8BW,SX81WCD
SX8BW,SX81W,SX81D
SX8BW,SX786还有错,楼主认真点,都没冒牌认真
的函数在这里用不了
还有什么错呢?
create function fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns varchar(8000)
as begin
declare @i int
declare @temp table (
x varchar(200)
)
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr) while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1)) set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end if @inputstr <> '' and @inputstr <> @seprator
insert @temp values(@inputstr)
declare @r varchar(8000)
set @r=''
select @r=@r+@seprator+x from @temp group by x
if @r<>''
set @r=stuff(@r,1,len(@seprator),'')
return @r
end
go--搂主要求
declare @t table (
x varchar(200)
)
insert @t select
'SX8BW,SX8BW,SX8BW,SX8BD'
union all select
'SX8BW,SX8BW,81WCD'
union all select
'SX8BW,SX8BW,SX8BW,SX8BW,SX8BW,SX81W,SX81W,SX81D'
union all select
'SX8BW,SX8BW,SX8BW,SX786'select dbo.fn_split(x,',') as x from @t--结果
x
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SX8BD,SX8BW
81WCD,SX8BW
SX81D,SX81W,SX8BW
SX786,SX8BW(所影响的行数为 4 行)
RETURNS varchar(8000)
AS
BEGIN
declare @ReturnTable TABLE(ListItem varchar(1000))
declare @Index int,@NewText varchar(8000)
SET @Index = CHARINDEX(@separator, @List)
WHILE NOT(@Index = 0)
BEGIN
SET @NewText = RTRIM(LTRIM(LEFT(@List, @Index - 1)))
SET @List = RIGHT(@List, LEN(@List) - @Index)
INSERT INTO @ReturnTable(ListItem) VALUES(@NewText)
SET @Index = CHARINDEX(@separator, @List)
END
INSERT INTO @ReturnTable(ListItem) VALUES(RTRIM(LTRIM(@List)))
declare @str varchar(8000)
set @str=''
select @str=@str+','+ListItem from (select distinct ListItem from @ReturnTable) T
RETURN stuff(@str,1,1,'')
ENDcreate table T (c1 varchar(100))
insert into T select 'SX8BW,SX8BW,SX8BW,8BD'
insert into T select 'SX8BW,SX8BW,SX8BW,SX8BW,SX8BW,SX81W,SX81W,SX81D'select dbo.SplitList(',',c1) from T group by c1----------
8BD,SX8BW
SX81D,SX81W,SX8BW
Returns varchar(1000)
As
Begin
Declare @rtnValue varchar(1000),@str varchar(6)
Set @rtnValue = '' While CharIndex(',',@strInput) > 1
Begin
Set @str = Left(@strInput,CharIndex(',',@strInput)) IF Len(@str) = 6 And CharIndex(@str,@rtnValue) = 0
Begin
Set @rtnValue = @rtnValue + @str
End
Set @strInput = substring(@strInput,CharIndex(',',@strInput)+1,Len(@strInput))
End IF Len(@strInput) = 5 And CharIndex(@strInput,@rtnValue) = 0
Set @rtnValue = @rtnValue + @strInput + ',' Set @rtnValue = Stuff(@rtnValue,Len(@rtnValue),1,'') Return(@rtnValue)
End
因为不管怎么样大家都帮助了我,这样吧
祝福:{CSDMN(冒牌经理 V0.3)
playwarcraft(时间就像乳沟,挤挤还是有的)
lzhs(快乐至上) }
三位一下
一人三分哈,好不好啊?君子还在乎分多少的问题吗?对不?
一人两分~
谢谢!
但是可以不用表变量,直接替换就可以create function fun_split
(
@a1 varchar(8000),
@s1 varchar(10)
)
returns varchar(8000)asbegindeclare @d varchar(100)
declare @e varchar(8000)
declare @i int--set @a1='111,222,111,222,333,444,555'
--set @a1=''
set @a1=@a1+@s1set @i=charindex(@s1,@a1)
set @e=''
while @i>0
begin
set @d=left(@a1,@i)
set @e=@e+@d
set @a1 = replace(substring(@a1, @i + 1, len(@a1) - @i),@d,'')
set @i=charindex(@s1,@a1)
endif @e<>'' set @e=left(@e,len(@e)-1)
return (@e)end
执行差不多
select dbo.fun_split(fieldname,',') as newfield from tablename
http://community.csdn.net/Expert/topic/4932/4932467.xml?temp=.3616754
的结贴明显欺负冒牌