怎么样判断一个长度很大的字符串是数字型的？？

Question

select isnumeric('123af451'),isnumeric('13346.125')
                        
----------- ----------- 
0           1（所影响的行数为 1 行）

Answer 1

ISNUMERIC
确定表达式是否为一个有效的数字类型。语法
ISNUMERIC ( expression )参数
expression要计算的表达式。返回类型
int注释
当输入表达式得数为一个有效的整数、浮点数、money 或 decimal 类型，那么 ISNUMERIC 返回 1；否则返回 0。返回值为 1 确保可以将 expression 转换为上述数字类型中的一种。示例
A. 使用 ISNUMERIC
下面的示例返回 1，这是因为 zip 列包含有效的数值。 USE pubs
SELECT ISNUMERIC(zip) 
FROM authors
GOB. 使用 ISNUMERIC 和 SUBSTRING
下面的示例对于 titles 表中的所有书名都返回 0，这是因为没有一个书名是有效的数值。USE pubs
GO
-- Because the title column is all character data, expect a result of 0
-- for the ISNUMERIC function.
SELECT SUBSTRING(title, 1, 15) type, price, ISNUMERIC(title)
FROM titles
GO下面是结果集：type            price                                  
--------------- -------------------------- ----------- 
The Busy Execut 19.99                      0           
Cooking with Co 11.95                      0           
You Can Combat  2.99                       0           
Straight Talk A 19.99                      0           
Silicon Valley  19.99                      0           
The Gourmet Mic 2.99                       0           
The Psychology  (null)                     0           
But Is It User  22.95                      0           
Secrets of Sili 20.00                      0           
Net Etiquette   (null)                     0           
Computer Phobic 21.59                      0           
Is Anger the En 10.95                      0           
Life Without Fe 7.00                       0           
Prolonged Data  19.99                      0           
Emotional Secur 7.99                       0           
Onions, Leeks,  20.95                      0           
Fifty Years in  11.95                      0           
Sushi, Anyone?  14.99                      0           (18 row(s) affected)

Answer 2

isnumeric(字段/变量/常量)
返回1,表示是数字,返回0表示不是数字

Answer 3

得补充一下马可的,isnumeric有点小BUG.declare @a varchar(100)
set @a='13346.125'
if  isnumeric(@a) and @a<>'.' and @a<>'-' and @a<>'+'
print 'Y'
else 
print 'N'

Answer 4

to:
j老师、马克、邹建。
函数isnumeric只支持小数点左边最多308位数字，小数点右边支持8000位数字
declare @a varchar(8000)
set @a=replicate('9',308)
print @a
if isnumeric(@a)=1
select 'aa'
else
select 'bb'
set @a=@a+'0'
print @a
if isnumeric(@a)=1
select 'aa'
else
select 'bb'该怎么办啊？？如果大于308位。

Answer 5

这个倒是可以，只是速度太慢：
select top 8000 identity(int,0,1) as id into # from sysobjects a,sysobjects bdeclare @a varchar(8000)
set @a=replicate('9',309)select id,substring(@a,id,1) 'substring(@a,id,1)' from # aa where (not exists (select 1 id from # where substring(@a,aa.id,1)=cast(id as varchar(10)))) and (id between 1 and len(@a))drop table #

Answer 6

if replace(
    reaplce(
     replace(
      replace(
       replace(
        replace(
         replace(
          replac(
           replace(
            replace(
             replace(@a,'0','')
                      ,'1','')
                     ,'2','')
                    ,'3','')
                   ,'4','')
                  ,'5','')
                 ,'6','')
                ,'7','')
               ,'8','')
              ,'9','')
             ,'.','')<>'' 
and @a<>'.'
print 'Y'
else
print 'N'

Answer 7

if replace(
    reaplce(
     replace(
      replace(
       replace(
        replace(
         replace(
          replace(
           replace(
            replace(
             replace(@a,'0','')
                      ,'1','')
                     ,'2','')
                    ,'3','')
                   ,'4','')
                  ,'5','')
                 ,'6','')
                ,'7','')
               ,'8','')
              ,'9','')
             ,'.','')<>'' 
and @a<>'.'
and len(@a)-len(replace(@a,'.',''))<=1
print 'Y'
else
print 'N'

Answer 8

to:j老师，
语句改为下面才可以运行，否则报错。declare @a varchar(100)
set @a='13346.125'
if (isnumeric(@a)=1 and  @a<>'.' and  @a<>'-'  and  @a<>'+')
print 'Y'
else 
print 'N'错误消息
服务器: 消息 156，级别 15，状态 1，行 5
Incorrect syntax near the keyword 'and'.

Answer 9

to:
j老师
declare @a varchar(8000)
set @a=replicate('9',309)
if replace(
    replace(
     replace(
      replace(
       replace(
        replace(
         replace(
          replace(
           replace(
            replace(
             replace(@a,'0','')
                      ,'1','')
                     ,'2','')
                    ,'3','')
                   ,'4','')
                  ,'5','')
                 ,'6','')
                ,'7','')
               ,'8','')
              ,'9','')
             ,'.','')<>'' 
and @a<>'.'
and len(@a)-len(replace(@a,'.',''))<=1
print 'Y'
else
print 'N'错误！！

Answer 10

to：
J老师
不好意思，
改成这样就行了：
declare @a varchar(8000)
set @a=replicate('9',309)
if replace(
    replace(
     replace(
      replace(
       replace(
        replace(
         replace(
          replace(
           replace(
            replace(
             replace(@a,'0','')
                      ,'1','')
                     ,'2','')
                    ,'3','')
                   ,'4','')
                  ,'5','')
                 ,'6','')
                ,'7','')
               ,'8','')
              ,'9','')
             ,'.','')='' －－修改此处
and @a<>'.'
and len(@a)-len(replace(@a,'.',''))<=1
print 'Y'
else
print 'N'

Answer 11

确实，忙中出错。哈哈但ISNUMERIC那句我在家里测语法上没错啊。只是过长了就变成N了。
太长只有用REPLACE。