/*
判断字符串中是否只有数字和英文字母的函数
declare @str varchar(500)
set @str = '4d)'
if dbo.IsOnlyNumAndEngth(@str) = 1
print '只包含数字与英文'
else
print '含有非数字与英文'
*/
CREATE function IsOnlyNumAndEngth(@str varchar (8000))
returns bit
as
begin
declare @i int
declare @j int
declare @k bit
declare @g char
set @i=len(@str)
set @j=1
set @k=1
if @str = ''
begin
set @k=0
end
while @j <=@i
begin
set @g = substring(@str,@j,1)
If(ascii(@g) Not in ( ascii('1'), ascii('2'), ascii('3'), ascii('4'), ascii('5'), ascii('6'), ascii('7'), ascii('8'), ascii('9'), ascii('0'),
ascii('Q'), ascii('W'), ascii('E'), ascii('R'), ascii('T'), ascii('Y'), ascii('U'), ascii('I'), ascii('O'), ascii('P'), ascii('A'), ascii('S'), ascii('D'), ascii('F'), ascii('G'), ascii('H'), ascii('J'), ascii('K'), ascii('L'), ascii('Z'), ascii('X'), ascii('C'), ascii('V'), ascii('B'), ascii('N'), ascii('M'),
ascii('q'), ascii('w'), ascii('e'), ascii('r'), ascii('t'), ascii('y'), ascii('u'), ascii('i'), ascii('o'), ascii('p'), ascii('a'), ascii('s'), ascii('d'), ascii('f'), ascii('g'), ascii('h'), ascii('j'), ascii('k'), ascii('l'), ascii('z'), ascii('x'), ascii('c'), ascii('v'), ascii('b'), ascii('n'), ascii('m')))
Begin
set @k=0
break
end
set @j=@j+1
end
return @k
end
注:
set @str = '58d '
dbo.IsOnlyNumAndEngth(@str)会为0