简单点的可以参考这个,但这个只是验证整数。 sn NOT LIKE '%[^0-9]%' 或者: sn LIKE REPLICATE( '[0-9]', LEN(sn));如果要验证的更好,需要自己去引入正则表达式。
试试这个:--drop table tbcreate table tb(v varchar(20))insert into tb select '123.456' union all select '$123.456ab' union all select 'abc11' union all select '$123.456' union all select '123' union all select '-123'go select * from tb where v not LIKE '%[^-0123456789.]%' /* v 123.456 */
这样判断有点问题,你试下我下面这个 insert into tb select '123.456' union all select '$123.456ab' union all select 'abc11' union all select '$123.456' union all select '123' union all select '-123'union all select '-'union all select '.'
go
select * from tb where v not LIKE '%[^-0123456789.]%'
这样判断有点问题,你试下我下面这个 insert into tb select '123.456' union all select '$123.456ab' union all select 'abc11' union all select '$123.456' union all select '123' union all select '-123'union all select '-'union all select '.'
go
select * from tb where v not LIKE '%[^-0123456789.]%'确实会把. 和 - 查出来。修改一下: --drop table tbcreate table tb(v varchar(20))insert into tb select '123.456' union all select '$123.456ab' union all select 'abc11' union all select '$123.456' union all select '123' union all select '-123'union all select '-'union all select '.'
go
select * from ( select * from tb where v not LIKE '%[^-0123456789.]%' )t where v not in ('.','-') /* v 123.456 123 -123 */
是业务要求这个你试了不: --drop table tbcreate table tb(v varchar(20))insert into tb select '123.456' union all select '$123.456ab' union all select 'abc11' union all select '$123.456' union all select '123' union all select '-123'union all select '-'union all select '.'
go
select * from ( select * from tb where v not LIKE '%[^-0123456789.]%' )t where v not in ('.','-') /* v 123.456 123 -123 */
sn NOT LIKE '%[^0-9]%'
或者:
sn LIKE REPLICATE( '[0-9]', LEN(sn));如果要验证的更好,需要自己去引入正则表达式。
select '123.456' union all
select '$123.456ab' union all
select 'abc11' union all
select '$123.456' union all
select '123' union all
select '-123'go
select *
from tb
where v not LIKE '%[^-0123456789.]%'
/*
v
123.456
*/
这样判断有点问题,你试下我下面这个
insert into tb
select '123.456' union all
select '$123.456ab' union all
select 'abc11' union all
select '$123.456' union all
select '123' union all
select '-123'union all
select '-'union all
select '.'
go
select *
from tb
where v not LIKE '%[^-0123456789.]%'
这样判断有点问题,你试下我下面这个
insert into tb
select '123.456' union all
select '$123.456ab' union all
select 'abc11' union all
select '$123.456' union all
select '123' union all
select '-123'union all
select '-'union all
select '.'
go
select *
from tb
where v not LIKE '%[^-0123456789.]%'确实会把. 和 - 查出来。修改一下:
--drop table tbcreate table tb(v varchar(20))insert into tb
select '123.456' union all
select '$123.456ab' union all
select 'abc11' union all
select '$123.456' union all
select '123' union all
select '-123'union all
select '-'union all
select '.'
go
select *
from
(
select *
from tb
where v not LIKE '%[^-0123456789.]%'
)t
where v not in ('.','-')
/*
v
123.456
123
-123
*/
--drop table tbcreate table tb(v varchar(20))insert into tb
select '123.456' union all
select '$123.456ab' union all
select 'abc11' union all
select '$123.456' union all
select '123' union all
select '-123'union all
select '-'union all
select '.'
go
select *
from
(
select *
from tb
where v not LIKE '%[^-0123456789.]%'
)t
where v not in ('.','-')
/*
v
123.456
123
-123
*/