最近要用到一个字符串的检测,传入形式为:1,2,3或者,112,2324,2,等数字型的串
避免其他形式的串录入,根据返回值来确定真假,调用方式如:if dbo.fn_chk_str(@a)=1
begin
.......
end
--基本方法:
create function fn_chk_str(@str varchar(2000))
returns int
begindeclare @outid int ,@TheStr varchar(2000)if right(@str,1)<>','
begin
set @str=@str+','
endset @outid=1--缺省设置为 if charindex(',',@str)=0
begin
set @outid=0
end else
begin
while charindex(',',@str)>0
begin
set @TheStr=left(@str,charindex(',',@str)-1) if isnumeric(@TheStr)=0
begin
set @outid=0
break
end
set @str=right(@str,len(@str)-charindex(',',@str)) end
endreturn @outid
end
避免其他形式的串录入,根据返回值来确定真假,调用方式如:if dbo.fn_chk_str(@a)=1
begin
.......
end
--基本方法:
create function fn_chk_str(@str varchar(2000))
returns int
begindeclare @outid int ,@TheStr varchar(2000)if right(@str,1)<>','
begin
set @str=@str+','
endset @outid=1--缺省设置为 if charindex(',',@str)=0
begin
set @outid=0
end else
begin
while charindex(',',@str)>0
begin
set @TheStr=left(@str,charindex(',',@str)-1) if isnumeric(@TheStr)=0
begin
set @outid=0
break
end
set @str=right(@str,len(@str)-charindex(',',@str)) end
endreturn @outid
end
也试一个:
create function fn_chk_str(@str varchar(2000))
returns bit
begin
set @str=replace(@str,',','')
while isnumeric(left(@str,308))=1
set @str=right(@str,(case when len(@str)>308 then len(@str)-308 else 0 end))
if @str=''
return 1
else
return 0
return null
end
go
select dbo.fn_chk_str('2872,87,695248764,9,876245')
/*
-----
1
*/
go
drop function fn_chk_str
create function fn_chk_str(@str varchar(2000))
returns bit
begin
set @str=replace(@str,',','')
while isnumeric(left(@str,308))=1 and charindex('e',left(@str,308))=0 and charindex('d',left(@str,308))=0
set @str=right(@str,(case when len(@str)>308 then len(@str)-308 else 0 end))
if @str=''
return 1
else
return 0
return null
end
go
select dbo.fn_chk_str('2872,87,695248764,9,876E245')
/*
-----
0
*/
go
drop function fn_chk_str
/*
-----
1(1 行受影响)
*/
ALTER FUNCTION fn_chk_str
(
@str VARCHAR(2000)
)
RETURNS BITBEGIN
IF @str LIKE '%,,%'
RETURN 0
IF @str LIKE REPLICATE('[,0-9]', LEN(@str))
RETURN 1
ELSE
RETURN 0
RETURN NULL
END
GO
SELECT dbo.fn_chk_str(',2872,112,345')
/*
-----
1(1 行受影响)
*/改一个
(
@str VARCHAR(2000)
)
RETURNS BITBEGIN
IF @str LIKE '%,,%' OR @str NOT LIKE '%[0-9]%'
RETURN 0
IF @str LIKE REPLICATE('[,0-9]', LEN(@str))
RETURN 1
ELSE
RETURN 0
RETURN NULL
END
GO改一下,必须存在一个以上的数字