create table tb(col varchar(10)) insert into tb values('aaa') insert into tb values('001') goselect * from tb where PATINDEX('%[^0-9]%',col) > 0drop table tb/* col ---------- aaa(所影响的行数为 1 行) */
declare @s varchar(50) set @s='54654546' if patindex('%[^0-9]%',@s)=0 select '哦了' else select '只能0-9'
create function my function(@str) as begin declare @result int;
declare @substr int; set @substr=@str; set @result =1; while len(@substr)>0 begin if(ascii(left(@substr,1)) >=48 and ascii(left(@substr,1)) <=57 ) begin @substr =right(@substr,len(@substr)-1); end else begin @result =0; break; end end if @result = 0 begin print "含有非数字字符" end else begin print "不含有非数字字符" end end
create table t (id varchar(5)) insert into tb values('ww') insert into tb values('11') select * from t where patindex('%[^0-9]%',id)>0drop table t
create table tb (id varchar(5)) insert into tb values('ww') insert into tb values('11') select * from tb where patindex('%[^0-9]%',id)>0drop table tb ww
create table tb(id varchar(5)) insert into tb values('ww') insert into tb values('11') select * from tb where isnumeric(id)=0 drop table tb
create table tb(col varchar(10)) insert into tb values('aaa') insert into tb values('01aaa') go select * from tb where PATINDEX('%[^0-9]%',col) > 0 drop table tb /*col ---------- aaa 01aaa(所影响的行数为 2 行)*/
insert into tb values('aaa')
insert into tb values('001')
goselect * from tb where PATINDEX('%[^0-9]%',col) > 0drop table tb/*
col
----------
aaa(所影响的行数为 1 行)
*/
set @s='54654546'
if patindex('%[^0-9]%',@s)=0
select '哦了'
else
select '只能0-9'
as
begin
declare @result int;
declare @substr int;
set @substr=@str;
set @result =1;
while len(@substr)>0
begin
if(ascii(left(@substr,1)) >=48 and ascii(left(@substr,1)) <=57 )
begin
@substr =right(@substr,len(@substr)-1);
end
else
begin
@result =0;
break;
end
end
if @result = 0
begin
print "含有非数字字符"
end
else
begin
print "不含有非数字字符"
end
end
create table t (id varchar(5))
insert into tb values('ww')
insert into tb values('11')
select * from t where patindex('%[^0-9]%',id)>0drop table t
create table tb (id varchar(5))
insert into tb values('ww')
insert into tb values('11')
select * from tb where patindex('%[^0-9]%',id)>0drop table tb
ww
create table tb(id varchar(5))
insert into tb values('ww')
insert into tb values('11')
select * from tb where isnumeric(id)=0
drop table tb
create table tb(col varchar(10))
insert into tb values('aaa')
insert into tb values('01aaa')
go select * from tb where PATINDEX('%[^0-9]%',col) > 0 drop table tb /*col
----------
aaa
01aaa(所影响的行数为 2 行)*/
select patindex('%[^(0-9)]%',@m_str)
-- 11select @m_str = '12345678901234567890'
select patindex('%[^(0-9)]%',@m_str)
-- 0