有没有一种简单的方法来确定一个varchar是否为数字?
示例:
abc123 --> 不是数字
123 --> 是数字
有没有一种简单的方法来确定一个varchar是否为数字?
示例:
abc123 --> 不是数字
123 --> 是数字
ISNUMERIC不适用 - 它告诉您该字符串可以转换为任何数值类型,这几乎总是一种无意义的信息。例如,根据ISNUMERIC以下所有内容都是数值:
£、$、0d0
如果您想检查数字且仅有数字,则需要使用负LIKE表达式:
not Value like '%[^0-9]%'
not Value like '%[^0-9]%'
意思是所有字符都是数字。这是一个双重否定。它断言 Value
中没有任何字符超出了 0-9
的范围。 - Damien_The_UnbelieverValue like '%[0-9]%'
将是“字符串包含至少一个数字字符”。Value like '%[^0-9]%'
将是“字符串包含至少一个非数字字符”。但是,再次强调,我的测试是 not Value like '%[^0-9]%'
,它否定了先前的测试,即“字符串不包含非数字字符”。 - Damien_The_UnbelieverISNUMERIC 可以实现此功能。
还要查看文章中的 NOTES 部分。
你可以这样检查:
declare @vchar varchar(50)
set @vchar ='34343';
select case when @vchar not like '%[^0-9]%' then 'Number' else 'Not a Number' end
如果你有特定的需求,使用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_CAST(@value AS INT) > 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_CAST(@value AS MONEY) IS NOT NULL
-- will allow scientific notation to be used like 1.7E+3
TRY_CAST(@value AS FLOAT) IS NOT NULL
我遇到了需要允许小数值的情况,所以我使用了not Value like '%[^0-9.]%'
.
, 2.2.2...1....0
, 2.1.2
等匹配。你需要添加第二个条件来排除有多个小数点或小数点不在两个数字之间的情况。 - The_RafiWade73 的小数解决方案并不完美。我修改了它,只允许一个小数点。
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;
Damien_The_Unbeliever指出,这种方式只适用于数字
Wade73添加了一些内容以处理小数点
neizan进行了另一个微调,而did notwhereuareat也做了相似的微调。
不幸的是,以上方法似乎都无法处理负值和带有逗号的值...
以下是我的微调,可识别负数和带有逗号的值。
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;
DECLARE @A nvarchar(100) = '12'
IF(ISNUMERIC(@A) = 1)
BEGIN
PRINT 'YES NUMERIC'
END
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;
要检查数字、货币和金额,请使用下面的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