select * from table where column_value is int
我知道我可以通过内部连接系统表和类型表来解决问题,但我想知道是否有更加优雅的方法。
请注意,column_value是一个varchar类型的字段,它“可能”有一个int值,但不一定。
也许我可以将其强制转换并捕获错误?但这似乎是一种曲线救国的方法。
select * from table where column_value is int
我知道我可以通过内部连接系统表和类型表来解决问题,但我想知道是否有更加优雅的方法。
请注意,column_value是一个varchar类型的字段,它“可能”有一个int值,但不一定。
也许我可以将其强制转换并捕获错误?但这似乎是一种曲线救国的方法。
select * from table
where column_value not like '[^0-9]'
where column_value like '[+-]%'
and substring(column_value,patindex('[+-]',substring(column_value,1))+1,len(column_value))
not like '[^0-9]'
如果column_value可能是超出“int”类型限制的整数,并且您希望排除这些情况,则需要更多的代码。
如果你想要实现自定义函数:
CREATE Function dbo.IsInteger(@Value VARCHAR(18))
RETURNS BIT
AS
BEGIN
RETURN ISNULL(
(SELECT CASE WHEN CHARINDEX('.', @Value) > 0 THEN
CASE WHEN CONVERT(int, PARSENAME(@Value, 1)) <> 0 THEN 0 ELSE 1 END
ELSE 1
END
WHERE ISNUMERIC(@Value + 'e0') = 1), 0)
END
当输入表达式可以转换为有效的整数、浮点数、货币或十进制类型时,ISNUMERIC返回1;否则返回0。返回值为1保证该表达式可以转换为这些数字类型之一。
我会像Svetlozar Angelov建议的那样做一个UDF,但我会先检查ISNUMERIC(如果不是,则返回0),然后检查column_value % 1 = 0
以查看它是否为整数。
这里是主体可能看起来像什么。您必须将模数逻辑放在单独的分支中,因为如果值不是数字,它将引发异常。
DECLARE @RV BIT
IF ISNUMERIC(@value) BEGIN
IF CAST(@value AS NUMERIC) % 1 = 0 SET @RV = 1
ELSE SET @RV = 0
END
ELSE SET @RV = 0
RETURN @RV
这应该处理所有情况而不抛出任何异常:
--This handles dollar-signs, commas, decimal-points, and values too big or small,
-- all while safely returning an int.
DECLARE @IntString as VarChar(50) = '$1,000.'
SELECT CAST((CASE WHEN --This IsNumeric check here does most of the heavy lifting. The rest is Integer-Specific
ISNUMERIC(@IntString) = 1
--Only allow Int-related characters. This will exclude things like 'e' and other foreign currency characters.
AND @IntString NOT LIKE '%[^ $,.\-+0-9]%' ESCAPE '\'--'
--Checks that the value is not out of bounds for an Integer.
AND CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(38)) BETWEEN -2147483648 AND 2147483647
--This allows values with decimal-points for count as an Int, so long as there it is not a fractional value.
AND CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(38)) = CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(38,2))
--This will safely convert values with decimal points to casting later as an Int.
THEN CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(10))
END) as Int)[Integer]
将此代码放入标量UDF中并调用ReturnInt()。
如果返回值为NULL,则它不是int(因此您需要IsInteger())
如果您不喜欢键入“WHERE ReturnInt(SomeValue) IS NOT NULL”,则可以将其放入另一个标量UDF中,称为IsInt(),以调用此函数并简单地返回“ReturnInt(SomeValue) IS NOT NULL”。
很酷的是,UDF可以通过返回“安全”转换的int值来发挥双倍作用。
仅仅因为某些东西可能是int,并不意味着将其强制转换为int不会引发巨大的异常。这个函数可以为您解决这个问题。
此外,我建议避免其他解决方案,因为这种通用方法将处理逗号、小数点、美元符号,并检查可接受的Int值范围,而其他解决方案则不会-或者它们需要多个SET操作,这会阻止您使用Scalar-Function的逻辑以实现最大性能。
请参见下面的示例,并针对我的代码和其他代码进行测试:
--Proves that appending "e0" or ".0e0" is NOT a good idea.
select ISNUMERIC('$1' + 'e0')--Returns: 0.
select ISNUMERIC('1,000' + 'e0')--Returns: 0.
select ISNUMERIC('1.0' + '.0e0')--Returns: 0.
--While these are numeric, they WILL break your code
-- if you try to cast them directly as int.
select ISNUMERIC('1,000')--Returns: 1.
select CAST('1,000' as Int)--Will throw exception.
select ISNUMERIC('$1')--Returns: 1.
select CAST('$1' as Int)--Will throw exception.
select ISNUMERIC('10.0')--Returns: 1.
select CAST('10.0' as Int)--Will throw exception.
select ISNUMERIC('9999999999223372036854775807')--Returns: 1. This is why I use Decimal(38) as Decimal defaults to Decimal(18).
select CAST('9999999999223372036854775807' as Int)--Will throw exception.
更新:
我在这里看到有人想要将类似于“123.”的值解析为整数。我已经更新了我的代码来处理这个问题。
注意:这会将“1.0”转换,但对于“1.9”则返回null。
如果您想要允许四舍五入,则可以调整“THEN”子句中的逻辑,添加Round(),如下所示:
ROUND(CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(10)), 0)
您必须还要删除检查“小数点”的“AND”,以允许四舍五入或截断。
DECLARE @TestValue nvarchar(MAX)
SET @TestValue = '1.04343234e5'
SELECT CASE WHEN ISNUMERIC(@TestValue) = 1
THEN CASE WHEN ROUND(@TestValue,0,1) = @TestValue
THEN 1
ELSE 0
END
ELSE null
END AS Analysis
如果你只是想验证一个字符串是否全是数字而不仅仅是可转换为INT,你可以做这个非常糟糕的事情:
select LEN(
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
'-1.223344556677889900e-1'
,'0','') ,'1','') ,'2','') ,'3','') ,'4','') ,'5','') ,'6','') ,'7','') ,'8','') ,'9','')
)
当字符串为空或仅包含数字时,它返回0。
为了将其作为“穷人版”整数的有用检查,您需要处理空字符串和初始负号。并手动确保它不会太长以适应您的INTEGER类型。