检查 varchar 是否是数字

Question

有没有一种简单的方法可以判断 varchar 是否是数字？

示例：

abc123 -->没有号码

123 -->是的，它是一个数字

Answer 1

要检查数字、货币和金额，请使用以下 SQL 片段。

@value NOT LIKE '%[^0-9.,]%'

为了快速获胜，请参考以下示例：

函数示例：

CREATE FUNCTION [dbo].[fnCheckValueIsNumber](
    @value NVARCHAR(255)=NULL
)RETURNS INT  AS BEGIN
    DECLARE @ReturnValue INT=0
    IF EXISTS (SELECT * WHERE @value NOT LIKE '%[^0-9.,]%') SELECT @ReturnValue=1
RETURN @ReturnValue;

执行结果

SELECT [dbo].[fnCheckValueIsNumber]('12345')
RESULT = 1

SELECT [dbo].[fnCheckValueIsNumber]('10020.25')
RESULT = 1

SELECT [dbo].[fnCheckValueIsNumber]('10,020.25')
RESULT = 1

SELECT [dbo].[fnCheckValueIsNumber]('12,345ABCD')
RESULT = 0

Answer 2

ISNUMERIC 不会这样做 - 它告诉您字符串可以转换为任何数字类型，这几乎总是一条毫无意义的信息。例如，根据 ISNUMERIC，以下所有内容都是数字：

英镑，$，0d0

如果您想检查数字且仅检查数字，则需要使用负 LIKE 表达式：

not Value like '%[^0-9]%'

Answer 3

ISNUMERIC 也可以

检查文章中的“注释”部分。

Answer 4

你可以这样检查：

declare @vchar varchar(50)
set @vchar ='34343';
select case when @vchar not like '%[^0-9]%' then 'Number' else 'Not a Number' end

Answer 5

使用 SQL Server 2012+，如果您有特定需求，可以使用 TRY_* 函数。例如，

-- will fail for decimal values, but allow negative values
TRY_CAST(@value AS INT) IS NOT NULL 

-- will fail for non-positive integers; can be used with other examples below as well, or reversed if only negative desired
TRY_CONVERT(INT, @value,) > 0

-- will fail if a $ is used, but allow decimals to the specified precision
TRY_CAST(@value AS DECIMAL(10,2)) IS NOT NULL 

-- will allow valid currency
TRY_CONVERT(MONEY,@value) IS NOT NULL  

-- will allow scientific notation to be used like 1.7E+3
TRY_CAST(@value AS FLOAT) IS NOT NULL 

Answer 6

我遇到了允许十进制值的需要，因此我使用了 not Value like '%[^0-9.]%'

Answer 7

Wade73 对小数的回答 不'不太有效。我已将其修改为仅允许一个小数点。

declare @MyTable table(MyVar nvarchar(10));
insert into @MyTable (MyVar)
values
    (N'1234')
    , (N'000005')
    , (N'1,000')
    , (N'293.8457')
    , (N'x')
    , (N'+')
    , (N'293.8457.')
    , (N'......');

-- This shows that Wade73's answer allows some non-numeric values to slip through.
select * from (
    select
        MyVar
        , case when MyVar not like N'%[^0-9.]%' then 1 else 0 end as IsNumber
    from
        @MyTable
) t order by IsNumber;

-- Notice the addition of "and MyVar not like N'%.%.%'".
select * from (
    select
        MyVar
        , case when MyVar not like N'%[^0-9.]%' and MyVar not like N'%.%.%' then 1 else 0 end as IsNumber
    from
        @MyTable
) t
order by IsNumber;

Answer 8

Damien_The_Unknowner 指出他的唯一适合数字

Wade73 添加了位来处理小数点

neizan 进行了额外的调整没有发生。

不幸的是，似乎没有一个能够处理负值，并且它们似乎在值中存在逗号问题...

这是我的调整，以获取负值和带有逗号的值

declare @MyTable table(MyVar nvarchar(10));
insert into @MyTable (MyVar) 
values 
(N'1234')
, (N'000005')
, (N'1,000')
, (N'293.8457')
, (N'x')
, (N'+')
, (N'293.8457.')
, (N'......')
, (N'.')
, (N'-375.4')
, (N'-00003')
, (N'-2,000')
, (N'3-3')
, (N'3000-')
;

-- This shows that Neizan's answer allows "." to slip through.
select * from (
select 
    MyVar
    , case when MyVar not like N'%[^0-9.]%' then 1 else 0 end as IsNumber 
from 
    @MyTable
) t order by IsNumber;

-- Notice the addition of "and MyVar not like '.'".
select * from (
select 
    MyVar
    , case when MyVar not like N'%[^0-9.]%' and MyVar not like N'%.%.%' and MyVar not like '.' then 1 else 0 end as IsNumber 
from 
    @MyTable
) t 
order by IsNumber;

--Trying to tweak for negative values and the comma
--Modified when comparison
select * from (
select 
    MyVar
    , case 
        when MyVar not like N'%[^0-9.,-]%' and MyVar not like '.' and isnumeric(MyVar) = 1 then 1
        else 0 
    end as IsNumber 
from 
    @MyTable
) t 
order by IsNumber;

Answer 9

DECLARE @A nvarchar(100) = '12'
IF(ISNUMERIC(@A) = 1)
BEGIN
    PRINT 'YES NUMERIC'
END

Answer 10

Neizan 的代码 让值仅一个“。”通过。冒着过于迂腐的风险，我又添加了一个 AND 子句。

declare @MyTable table(MyVar nvarchar(10));
insert into @MyTable (MyVar) 
values 
    (N'1234')
    , (N'000005')
    , (N'1,000')
    , (N'293.8457')
    , (N'x')
    , (N'+')
    , (N'293.8457.')
    , (N'......')
    , (N'.')
    ;

-- This shows that Neizan's answer allows "." to slip through.
select * from (
    select 
        MyVar
        , case when MyVar not like N'%[^0-9.]%' then 1 else 0 end as IsNumber 
    from 
        @MyTable
) t order by IsNumber;

-- Notice the addition of "and MyVar not like '.'".
select * from (
    select 
        MyVar
        , case when MyVar not like N'%[^0-9.]%' and MyVar not like N'%.%.%' and MyVar not like '.' then 1 else 0 end as IsNumber 
    from 
        @MyTable
) t 
order by IsNumber;

Answer 11

不要忘记从数据中排除回车！

如：

SELECT 
  Myotherval
  , CASE WHEN TRIM(REPLACE([MyVal], char(13) + char(10), '')) not like '%[^0-9]%' and RTRIM(REPLACE([MyVal], char(13) + char(10), '')) not like '.' and isnumeric(REPLACE([MyVal], char(13) + char(10), '')) = 1 THEN 'my number: ' +  [MyVal]
             ELSE ISNULL(Cast([MyVal] AS VARCHAR(8000)), '')
        END AS 'MyVal'
FROM MyTable

Answer 12

如果您想在字段上添加约束：

固定长度的正整数

ALTER TABLE dbo.BankBranchType  
ADD CONSTRAINT CK_TransitNumberMustBe5Digits 
CHECK (TransitNumber NOT like '%[^0-9]%'  
       AND LEN(TransitNumber) = 5)