因为isnumeric对'-'号也会返回1,所以要再加个条件 select * from test where isnumeric(a)=1 and a<>'-'
select * from (select 'a11' as a, 10 as b union select 'abvc', 11 union select '123', 11) test where a not like '%[^0-9]%' /* a b 123 11 */
select * from test where isnumeric(a) = 1 and charindex('-',a) = 0 and charindex('.',a) = 0
判断一个字符串是否是“纯数字”的,建议不要用isnumeric,因为isnumeric('-1,234.56'), isnumeric('-1.12E+2'), isnumeric('-1.12e+2')的结果都是1,需要另外去除的字符有 + - , . E e(知道有这些,还有没有其它的就不知道了),建议使用SQL提供的like功能not like '%[^0-9]%'
真是一个比一个完美.综合之后的语句(要去掉小数点加号减号)select * from chinakidsuser.dbo.view_user where isnumeric(logid) = 1 and charindex('-',logid) = 0 and charindex('+',logid) = 0 and charindex('.',logid) = 0
有多了一个需要去除的字符:isnumeric('-$2.00') 的结果也是 1
又多了一个需要去除的字符 $isnumeric('-$2.00') 的结果也是 1
最佳组合:看看有没有问题select * from (select * from chinakidsuser.dbo.view_user where isnumeric(logid)=1) as a where a.logid not like '%[^0-9]%'
select tablename.* from tablename where isnumeric(tablename.a)=1
直接这样就行了,因为满足not like '%[^0-9]%'的字符串一定可以使isnumeric为1 select * from chinakidsuser.dbo.view_user where logid not like '%[^0-9]%'
纠正一下,满足not like '%[^0-9]%'的肯定是“纯数字”,但是不一定可以使isnumeric为1,例如一个长度超过309位的字符串就不行,因为转换出来后已经超出了SQL能够表示的数字范围
不一样.我试了一下select count(*) from (select * from chinakidsuser.dbo.view_user where isnumeric(logid)=1) as a where a.logid not like '%[^0-9]%'select count(*) from chinakidsuser.dbo.view_user where logid not like '%[^0-9]%'第一语句的结果是 10092行.第二语句的结果是10251行.也就是说第二句可能匹配的不完全
那这样,LZ试试执行下面的语句,看看是那些不匹配,看看这些不匹配的数据是不是LZ想要的,我也想看看 select * from chinakidsuser.dbo.view_user where logid not like '%[^0-9]%' and isnumeric(logid) = 0
真相大白. 846123 85911422 86452257 888801 940228 940310 这些全角数字 logid not like '%[^0-9]%' 也是把它们包含在内的
总结一下.dulei115老兄看看我说的对不对?如果要实现我这个只取数字的需求, logid not like '%[^0-9]%' 是可以实现的.但这个结果集里面有一些全角数字,或者是全角半角混合的数字. 如果用isnumeric函数的话,可能有一些比较长的数字就不会返回.所以不能用.
哦,那看LZ想不想要这些,想要就直接 select * from chinakidsuser.dbo.view_user where logid not like '%[^0-9]%' 不想要就这样 select * from chinakidsuser.dbo.view_user where logid not like '%[^0-9]%' and isnumeric(logid) = 1
说的很对,刚才我用.net语法试过了.全角的数字也可以用int.parse()转成int 类型. 所以返回的结果集中可以包含这些BT的全角数字. 我最终采用以下这句. select * from chinakidsuser.dbo.view_user where logid not like '%[^0-9]%'谢谢~结了~
select * from test where isnumeric(a)=1 and a<>'-'
from (select 'a11' as a, 10 as b
union select 'abvc', 11
union select '123', 11) test
where a not like '%[^0-9]%'
/*
a b
123 11
*/
from test
where isnumeric(a) = 1 and
charindex('-',a) = 0 and
charindex('.',a) = 0
from chinakidsuser.dbo.view_user
where isnumeric(logid) = 1 and
charindex('-',logid) = 0 and
charindex('+',logid) = 0 and
charindex('.',logid) = 0
from (select * from chinakidsuser.dbo.view_user where isnumeric(logid)=1) as a
where a.logid not like '%[^0-9]%'
select *
from chinakidsuser.dbo.view_user
where logid not like '%[^0-9]%'
from (select * from chinakidsuser.dbo.view_user where isnumeric(logid)=1) as a
where a.logid not like '%[^0-9]%'select count(*)
from chinakidsuser.dbo.view_user
where logid not like '%[^0-9]%'第一语句的结果是 10092行.第二语句的结果是10251行.也就是说第二句可能匹配的不完全
select *
from chinakidsuser.dbo.view_user
where logid not like '%[^0-9]%' and isnumeric(logid) = 0
846123
85911422
86452257
888801
940228
940310
这些全角数字 logid not like '%[^0-9]%' 也是把它们包含在内的
如果用isnumeric函数的话,可能有一些比较长的数字就不会返回.所以不能用.
select *
from chinakidsuser.dbo.view_user
where logid not like '%[^0-9]%'
不想要就这样
select *
from chinakidsuser.dbo.view_user
where logid not like '%[^0-9]%' and isnumeric(logid) = 1
所以返回的结果集中可以包含这些BT的全角数字.
我最终采用以下这句.
select *
from chinakidsuser.dbo.view_user
where logid not like '%[^0-9]%'谢谢~结了~
地址: http://hi.baidu.com/flying02/blog/item/b9fdba0eb88e78e737d12241.html
中isnumeric(a)这个函数有bug,正如dulei115所说,他会把科学记数法当着字符串,建议自己设计一个函数判断。