SQL Server，哪里字段是int？

发布于 2024-08-03 08:55:37 字数 228 浏览 8 评论 0原文

我怎样才能完成：

select * from table where column_value is int

我知道我可能可以内部连接到系统表和类型表，但我想知道是否有更优雅的方法。

请注意，column_value 是一个 varchar，“可以”具有 int，但不一定。

也许我可以直接投射它并捕获错误？但同样，这看起来像是一个黑客行为。

原文

how can I accomplish:

select * from table where column_value is int

I know I can probably inner join to the system tables and type tables but I'm wondering if there's a more elegant way.

Note that column_value is a varchar that "could" have an int, but not necessarily.

Maybe I can just cast it and trap the error? But again, that seems like a hack.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

寄与心 2024-08-10 08:55:37

select * from table
where column_value not like '[^0-9]'

如果允许使用负整数，则需要类似 You

where column_value like '[+-]%' 
and substring(column_value,patindex('[+-]',substring(column_value,1))+1,len(column_value))
not like '[^0-9]'

need more code if column_value can be an integer that就会超出“int”类型的限制，并且您希望排除这种情况。

select * from table
where column_value not like '[^0-9]'

If negative ints are allowed, you need something like

where column_value like '[+-]%' 
and substring(column_value,patindex('[+-]',substring(column_value,1))+1,len(column_value))
not like '[^0-9]'

You need more code if column_value can be an integer that exceeds the limits of the "int" type, and you want to exclude such cases.

回复收藏 0 原文

怪异←思 2024-08-10 08:55:37

如果您想实现您的自定义功能，请在此处

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 保证表达式可以转换为以下之一
这些数字类型。

Here if you want to implement your custom function

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 returns 1 when the input
expression evaluates to a valid
integer, floating point number, money
or decimal type; otherwise it returns
0. A return value of 1 guarantees that expression can be converted to one of
these numeric types.

回复收藏 0 原文

爱已欠费 2024-08-10 08:55:37

我会按照 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

I would do a UDF as Svetlozar Angelov suggests, but I would check for ISNUMERIC first (and return 0 if not), and then check for column_value % 1 = 0 to see if it's an integer.

Here's what the body might look like. You have to put the modulo logic in a separate branch because it will throw an exception if the value isn't numeric.

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

回复收藏 0 原文

伴随着你 2024-08-10 08:55:37

这应该处理所有情况而不抛出任何异常：

--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,'
将其放入标量 UDF 中并调用它ReturnInt()。


如果值返回为 NULL，则它不是 int（因此存在 IsInteger() 要求）
如果您不喜欢键入“WHERE ReturnInt(SomeValue) IS NOT NULL”，您可以将其放入另一个名为 IsInt() 的标量 UDF 中来调用此函数并简单地返回“ReturnInt(SomeValue) IS NOT NULL”。
很酷的是，UDF 可以通过安全地返回“”转换后的 int 值来发挥双重作用。


仅仅因为某些东西可以是一个 int 并不意味着将其转换为 int 不会抛出一个巨大的异常。这会为您解决这个问题。
另外，我会避免使用其他解决方案，因为这种通用方法将处理逗号、小数、美元符号，并检查可接受的 Int 值的范围，而其他解决方案则不会 - 或者它们需要多个 SET 操作，从而阻止您使用中的逻辑实现最佳性能的标量函数。
请参阅下面的示例，并根据我的代码和其他代码对其进行测试：
--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”以允许舍入或截断。
,''),',','') 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,'
将其放入标量 UDF 中并调用它ReturnInt()。


如果值返回为 NULL，则它不是 int（因此存在 IsInteger() 要求）
如果您不喜欢键入“WHERE ReturnInt(SomeValue) IS NOT NULL”，您可以将其放入另一个名为 IsInt() 的标量 UDF 中来调用此函数并简单地返回“ReturnInt(SomeValue) IS NOT NULL”。
很酷的是，UDF 可以通过安全地返回“”转换后的 int 值来发挥双重作用。


仅仅因为某些东西可以是一个 int 并不意味着将其转换为 int 不会抛出一个巨大的异常。这会为您解决这个问题。
另外，我会避免使用其他解决方案，因为这种通用方法将处理逗号、小数、美元符号，并检查可接受的 Int 值的范围，而其他解决方案则不会 - 或者它们需要多个 SET 操作，从而阻止您使用中的逻辑实现最佳性能的标量函数。
请参阅下面的示例，并根据我的代码和其他代码对其进行测试：

更新：


我在这里读到一条评论，您希望能够解析像“123”这样的值。变成一个整数。我也更新了我的代码来处理这个问题。
 注意： 这会转换“1.0”，但在“1.9”上返回 null。


如果您想允许舍入，请调整“THEN”子句中的逻辑以添加 Round() ，如下所示：


ROUND(CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(10)), 0)


您还必须删除检查“小数点”的“AND”以允许舍入或截断。
,''),',','') as Decimal(38)) = CAST(REPLACE(REPLACE(@IntString,'
将其放入标量 UDF 中并调用它ReturnInt()。


如果值返回为 NULL，则它不是 int（因此存在 IsInteger() 要求）
如果您不喜欢键入“WHERE ReturnInt(SomeValue) IS NOT NULL”，您可以将其放入另一个名为 IsInt() 的标量 UDF 中来调用此函数并简单地返回“ReturnInt(SomeValue) IS NOT NULL”。
很酷的是，UDF 可以通过安全地返回“”转换后的 int 值来发挥双重作用。


仅仅因为某些东西可以是一个 int 并不意味着将其转换为 int 不会抛出一个巨大的异常。这会为您解决这个问题。
另外，我会避免使用其他解决方案，因为这种通用方法将处理逗号、小数、美元符号，并检查可接受的 Int 值的范围，而其他解决方案则不会 - 或者它们需要多个 SET 操作，从而阻止您使用中的逻辑实现最佳性能的标量函数。
请参阅下面的示例，并根据我的代码和其他代码对其进行测试：

更新：


我在这里读到一条评论，您希望能够解析像“123”这样的值。变成一个整数。我也更新了我的代码来处理这个问题。
 注意： 这会转换“1.0”，但在“1.9”上返回 null。


如果您想允许舍入，请调整“THEN”子句中的逻辑以添加 Round() ，如下所示：


ROUND(CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(10)), 0)


您还必须删除检查“小数点”的“AND”以允许舍入或截断。
,''),',','') as Decimal(38,2))
                       --This will safely convert values with decimal points to casting later as an Int.
                  THEN CAST(REPLACE(REPLACE(@IntString,'
将其放入标量 UDF 中并调用它ReturnInt()。


如果值返回为 NULL，则它不是 int（因此存在 IsInteger() 要求）
如果您不喜欢键入“WHERE ReturnInt(SomeValue) IS NOT NULL”，您可以将其放入另一个名为 IsInt() 的标量 UDF 中来调用此函数并简单地返回“ReturnInt(SomeValue) IS NOT NULL”。
很酷的是，UDF 可以通过安全地返回“”转换后的 int 值来发挥双重作用。


仅仅因为某些东西可以是一个 int 并不意味着将其转换为 int 不会抛出一个巨大的异常。这会为您解决这个问题。
另外，我会避免使用其他解决方案，因为这种通用方法将处理逗号、小数、美元符号，并检查可接受的 Int 值的范围，而其他解决方案则不会 - 或者它们需要多个 SET 操作，从而阻止您使用中的逻辑实现最佳性能的标量函数。
请参阅下面的示例，并根据我的代码和其他代码对其进行测试：

更新：


我在这里读到一条评论，您希望能够解析像“123”这样的值。变成一个整数。我也更新了我的代码来处理这个问题。
 注意： 这会转换“1.0”，但在“1.9”上返回 null。


如果您想允许舍入，请调整“THEN”子句中的逻辑以添加 Round() ，如下所示：


ROUND(CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(10)), 0)


您还必须删除检查“小数点”的“AND”以允许舍入或截断。
,''),',','') as Decimal(10))
             END) as Int)[Integer]

将其放入标量 UDF 中并调用它ReturnInt()。

如果值返回为 NULL，则它不是 int（因此存在 IsInteger() 要求）

如果您不喜欢键入“WHERE ReturnInt(SomeValue) IS NOT NULL”，您可以将其放入另一个名为 IsInt() 的标量 UDF 中来调用此函数并简单地返回“ReturnInt(SomeValue) IS NOT NULL”。

很酷的是，UDF 可以通过安全地返回“”转换后的 int 值来发挥双重作用。

仅仅因为某些东西可以是一个 int 并不意味着将其转换为 int 不会抛出一个巨大的异常。这会为您解决这个问题。

另外，我会避免使用其他解决方案，因为这种通用方法将处理逗号、小数、美元符号，并检查可接受的 Int 值的范围，而其他解决方案则不会 - 或者它们需要多个 SET 操作，从而阻止您使用中的逻辑实现最佳性能的标量函数。

请参阅下面的示例，并根据我的代码和其他代码对其进行测试：

更新：

我在这里读到一条评论，您希望能够解析像“123”这样的值。变成一个整数。我也更新了我的代码来处理这个问题。

注意： 这会转换“1.0”，但在“1.9”上返回 null。

如果您想允许舍入，请调整“THEN”子句中的逻辑以添加 Round() ，如下所示：

ROUND(CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(10)), 0)

您还必须删除检查“小数点”的“AND”以允许舍入或截断。

This should handle all cases without throwing any exceptions:

--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,'
Throw this into a Scalar UDF and call it ReturnInt().


If the value comes back as NULL, then it's not an int (so there's your IsInteger() requirement)
If you don't like typing "WHERE ReturnInt(SomeValue) IS NOT NULL", you could throw it into another scalar UDF called IsInt() to call this function and simply return "ReturnInt(SomeValue) IS NOT NULL".
The cool thing is, the UDF can serve double duty by returning the "safely" converted int value.


Just because something can be an int doesn't mean casting it as an int won't throw a huge exception.  This takes care of that for you.
Also, I'd avoid the other solutions because this universal approach will handle commas, decimals, dollar signs, and checks the acceptable Int value's range while the other solutions do not - or they require multiple SET operations that prevent you from using the logic in a Scalar-Function for maximum performance.
See the examples below and test them against my code and others:
--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.

Update:


I read a comment here that you want to be able to parse a value like '123.' into an Integer.  I have updated my code to handle this as well.
Note: This converts "1.0", but returns null on "1.9".


If you want to allow for rounding, then tweak the logic in the "THEN" clause to add Round() like so:


ROUND(CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(10)), 0)


You must also remove the "AND" that checks for "decimal-points" to allow for Rounding or Truncation.
,''),',','') 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,'
Throw this into a Scalar UDF and call it ReturnInt().


If the value comes back as NULL, then it's not an int (so there's your IsInteger() requirement)
If you don't like typing "WHERE ReturnInt(SomeValue) IS NOT NULL", you could throw it into another scalar UDF called IsInt() to call this function and simply return "ReturnInt(SomeValue) IS NOT NULL".
The cool thing is, the UDF can serve double duty by returning the "safely" converted int value.


Just because something can be an int doesn't mean casting it as an int won't throw a huge exception.  This takes care of that for you.
Also, I'd avoid the other solutions because this universal approach will handle commas, decimals, dollar signs, and checks the acceptable Int value's range while the other solutions do not - or they require multiple SET operations that prevent you from using the logic in a Scalar-Function for maximum performance.
See the examples below and test them against my code and others:

Update:


I read a comment here that you want to be able to parse a value like '123.' into an Integer.  I have updated my code to handle this as well.
Note: This converts "1.0", but returns null on "1.9".


If you want to allow for rounding, then tweak the logic in the "THEN" clause to add Round() like so:


ROUND(CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(10)), 0)


You must also remove the "AND" that checks for "decimal-points" to allow for Rounding or Truncation.
,''),',','') as Decimal(38)) = CAST(REPLACE(REPLACE(@IntString,'
Throw this into a Scalar UDF and call it ReturnInt().


If the value comes back as NULL, then it's not an int (so there's your IsInteger() requirement)
If you don't like typing "WHERE ReturnInt(SomeValue) IS NOT NULL", you could throw it into another scalar UDF called IsInt() to call this function and simply return "ReturnInt(SomeValue) IS NOT NULL".
The cool thing is, the UDF can serve double duty by returning the "safely" converted int value.


Just because something can be an int doesn't mean casting it as an int won't throw a huge exception.  This takes care of that for you.
Also, I'd avoid the other solutions because this universal approach will handle commas, decimals, dollar signs, and checks the acceptable Int value's range while the other solutions do not - or they require multiple SET operations that prevent you from using the logic in a Scalar-Function for maximum performance.
See the examples below and test them against my code and others:

Update:


I read a comment here that you want to be able to parse a value like '123.' into an Integer.  I have updated my code to handle this as well.
Note: This converts "1.0", but returns null on "1.9".


If you want to allow for rounding, then tweak the logic in the "THEN" clause to add Round() like so:


ROUND(CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(10)), 0)


You must also remove the "AND" that checks for "decimal-points" to allow for Rounding or Truncation.
,''),',','') as Decimal(38,2))
                       --This will safely convert values with decimal points to casting later as an Int.
                  THEN CAST(REPLACE(REPLACE(@IntString,'
Throw this into a Scalar UDF and call it ReturnInt().


If the value comes back as NULL, then it's not an int (so there's your IsInteger() requirement)
If you don't like typing "WHERE ReturnInt(SomeValue) IS NOT NULL", you could throw it into another scalar UDF called IsInt() to call this function and simply return "ReturnInt(SomeValue) IS NOT NULL".
The cool thing is, the UDF can serve double duty by returning the "safely" converted int value.


Just because something can be an int doesn't mean casting it as an int won't throw a huge exception.  This takes care of that for you.
Also, I'd avoid the other solutions because this universal approach will handle commas, decimals, dollar signs, and checks the acceptable Int value's range while the other solutions do not - or they require multiple SET operations that prevent you from using the logic in a Scalar-Function for maximum performance.
See the examples below and test them against my code and others:

Update:


I read a comment here that you want to be able to parse a value like '123.' into an Integer.  I have updated my code to handle this as well.
Note: This converts "1.0", but returns null on "1.9".


If you want to allow for rounding, then tweak the logic in the "THEN" clause to add Round() like so:


ROUND(CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(10)), 0)


You must also remove the "AND" that checks for "decimal-points" to allow for Rounding or Truncation.
,''),',','') as Decimal(10))
             END) as Int)[Integer]

Throw this into a Scalar UDF and call it ReturnInt().

If the value comes back as NULL, then it's not an int (so there's your IsInteger() requirement)

If you don't like typing "WHERE ReturnInt(SomeValue) IS NOT NULL", you could throw it into another scalar UDF called IsInt() to call this function and simply return "ReturnInt(SomeValue) IS NOT NULL".

The cool thing is, the UDF can serve double duty by returning the "safely" converted int value.

Just because something can be an int doesn't mean casting it as an int won't throw a huge exception. This takes care of that for you.

Also, I'd avoid the other solutions because this universal approach will handle commas, decimals, dollar signs, and checks the acceptable Int value's range while the other solutions do not - or they require multiple SET operations that prevent you from using the logic in a Scalar-Function for maximum performance.

See the examples below and test them against my code and others:

Update:

I read a comment here that you want to be able to parse a value like '123.' into an Integer. I have updated my code to handle this as well.

Note: This converts "1.0", but returns null on "1.9".

If you want to allow for rounding, then tweak the logic in the "THEN" clause to add Round() like so:

ROUND(CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(10)), 0)

You must also remove the "AND" that checks for "decimal-points" to allow for Rounding or Truncation.

回复收藏 0 原文

鲜肉鲜肉永远不皱 2024-08-10 08:55:37

为什么不使用以下内容并测试 1？

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

Why not use the following and test for 1?

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

回复收藏 0 原文

快乐很简单 2024-08-10 08:55:37

如果你纯粹想验证一个字符串是否全是数字，而不仅仅是能够转换为 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。

为了使其成为对“穷人”整数的有用检查，您必须处理空字符串和初始负号。并手动确保它对于您的整数类型来说不会太长。

If you are purely looking to verify a string is all digits and not just CAST-able to INT you can do this terrible, terrible thing:

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','')
)

It returns 0 when the string was empty or pure digits.

To make it a useful check for "poor-man's" Integer you'd have to deal with empty string, and an initial negative sign. And manually make sure it isn't too long for your variety of INTEGER.

回复收藏 0 原文

~没有更多了~