DECLARE @Str VARCHAR(20) SET @Str='1,2,3,4,4.5,6'SELECT STUFF((SELECT ','+CAST(num AS VARCHAR(20)) FROM ( SELECT DISTINCT CAST(t.c.value('.','DECIMAL(9,2)')AS int) AS num FROM( SELECT CONVERT(XML,'<x>'+replace(@Str,',','</x><x>')+'</x>') AS B) a CROSS APPLY a.B.nodes('/x') T(c) ) a FOR XML PATH('') ),1,1,'')
/* 1,2,3,4,6 */
试试这个:--drop function dbo.f_splitSTR create function dbo.f_splitSTR ( @str varchar(8000) --要分拆的字符串 ) returns varchar(8000) as begin declare @temp_str varchar(8000) declare @result_str varchar(8000)set @temp_str = @str+',' set @result_str =''while @temp_str like '%,%' begin select @result_str = @result_str + case when left(@temp_str,charindex(',',@temp_str)-1) like '%.%' then '' else ','+left(@temp_str,charindex(',',@temp_str)-1) end, @temp_str = stuff(@temp_str,1,charindex(',',@temp_str),''); endreturn stuff(@result_str,1,1,'') end go select t.*, dbo.f_splitSTR(t.v) vv from ( select '1,2,3,4,4.5,6' v union all select '1.5,2,3,4,4.5,6.6' )t /* v vv 1,2,3,4,4.5,6 1,2,3,4,6 1.5,2,3,4,4.5,6.6 2,3,4 */
SET @Str='1,2,3,4,4.5,6'SELECT STUFF((SELECT ','+CAST(num AS VARCHAR(20))
FROM (
SELECT DISTINCT CAST(t.c.value('.','DECIMAL(9,2)')AS int) AS num
FROM(
SELECT CONVERT(XML,'<x>'+replace(@Str,',','</x><x>')+'</x>') AS B) a
CROSS APPLY a.B.nodes('/x') T(c)
) a
FOR XML PATH('')
),1,1,'')
/*
1,2,3,4,6
*/
create function dbo.f_splitSTR
(
@str varchar(8000) --要分拆的字符串
)
returns varchar(8000)
as
begin
declare @temp_str varchar(8000)
declare @result_str varchar(8000)set @temp_str = @str+','
set @result_str =''while @temp_str like '%,%'
begin
select @result_str = @result_str + case when left(@temp_str,charindex(',',@temp_str)-1) like '%.%' then ''
else ','+left(@temp_str,charindex(',',@temp_str)-1)
end,
@temp_str = stuff(@temp_str,1,charindex(',',@temp_str),'');
endreturn stuff(@result_str,1,1,'')
end
go
select t.*,
dbo.f_splitSTR(t.v) vv
from
(
select '1,2,3,4,4.5,6' v
union all
select '1.5,2,3,4,4.5,6.6'
)t
/*
v vv
1,2,3,4,4.5,6 1,2,3,4,6
1.5,2,3,4,4.5,6.6 2,3,4
*/