select * from 表 where charindex(',,',字段)=0 and left(字段,1)!=',' and right(字段,1)!=',' and isnumeric(replace(字段,',',''))=1
if object_id('dbo.fn_split')is not null drop function dbo.fn_split go create function dbo.fn_split ( @inputstr varchar(8000), @seprator varchar(10) ) returns varchar(1000) as begin declare @temp table (a varchar(200)) declare @i int declare @s varchar(100) 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) select @s=isnull(@s+',','')+rtrim(ltrim(a)) from @temp where rtrim(ltrim(a))<>'' return @s end go --调用 declare @s varchar(1000) set @s=',,1,2,,3 ,,,,,1, 1,,,,,,3 ' select dbo.fn_split(@s,',') /* ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1,2,3,1,1,3 */
本帖最后由 libin_ftsafe 于 2009-02-12 13:31:20 编辑
if object_id('dbo.fn_split')is not null drop function dbo.fn_split go create function dbo.fn_split ( @inputstr varchar(8000), @seprator varchar(10) ) returns varchar(1000) as begin declare @temp table (a varchar(200)) declare @i int declare @s varchar(100) 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) select @s=isnull(@s+',','')+rtrim(ltrim(a)) from @temp where rtrim(ltrim(a))<>'' return @s end go --调用 declare @t table( col varchar(20)) insert @t select ',,1,2,,3' insert @t select ',,,,,1,' insert @t select '1,,,,,,3' select dbo.fn_split(col,',') from @t /*---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1,2,3 1 1,3*/
select * from tb where left(col,1) <> ',' and right(col,1) <> ',' and len(col) - len(replace(col,',','')) = len(col) / 2 + 1
如果只是1,2,3这样的数字, 直接select * from tb where left(col,1) <> ',' and right(col,1) <> ',' and len(col) - len(replace(col,',','')) = len(col) / 2 + 1即可.
declare @a nvarchar(50),@b nvarchar(50) set @a = ',,1,2,,3 ,,,,,1, 1,,,,,,3' set @b = '' while len(@a)>0 begin if left(@a,1) between '0' and '9' begin select @b = @b+','+left(@a,1) end set @a = right(@a,len(@a)-1) end select stuff(@b,1,1,'')-------------------------------------------------- 1,2,3,1,1,3(1 row(s) affected)
如果只是1,2,3这样的数字, select * from tb where left(col,1) <> ',' and right(col,1) <> ',' and len(col) - len(replace(col,',','')) = len(col) / 2 + 1 and charindex(',,',col) = 0如果还有11,12,3这样的数字, select * from tb where left(col,1) <> ',' and right(col,1) <> ',' and charindex(',,',col) = 0
select
*
from
表
where
charindex(',,',字段)=0
and
left(字段,1)!=','
and
right(字段,1)!=','
and
isnumeric(replace(字段,',',''))=1
go
create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns varchar(1000)
as
begin
declare @temp table (a varchar(200))
declare @i int
declare @s varchar(100)
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)
select @s=isnull(@s+',','')+rtrim(ltrim(a)) from @temp where rtrim(ltrim(a))<>''
return @s
end
go
--调用
declare @s varchar(1000)
set @s=',,1,2,,3 ,,,,,1, 1,,,,,,3 '
select dbo.fn_split(@s,',')
/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,2,3,1,1,3
*/
go
create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns varchar(1000)
as
begin
declare @temp table (a varchar(200))
declare @i int
declare @s varchar(100)
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)
select @s=isnull(@s+',','')+rtrim(ltrim(a)) from @temp where rtrim(ltrim(a))<>''
return @s
end
go
--调用
declare @t table( col varchar(20))
insert @t select ',,1,2,,3'
insert @t select ',,,,,1,'
insert @t select '1,,,,,,3'
select dbo.fn_split(col,',') from @t
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,2,3
1
1,3*/
直接select * from tb where left(col,1) <> ',' and right(col,1) <> ',' and len(col) - len(replace(col,',','')) = len(col) / 2 + 1即可.
set @a = ',,1,2,,3 ,,,,,1, 1,,,,,,3'
set @b = ''
while len(@a)>0
begin
if left(@a,1) between '0' and '9'
begin
select @b = @b+','+left(@a,1)
end
set @a = right(@a,len(@a)-1)
end
select stuff(@b,1,1,'')--------------------------------------------------
1,2,3,1,1,3(1 row(s) affected)
select * from tb where left(col,1) <> ',' and right(col,1) <> ',' and charindex(',,',col) = 0