验证sqll服务器中的整数值 [英] Validate integer value in sqll server

问题描述

我有一个表，其中一列允许空值或整数值我想在sql中使用case语句检查这个，如下所示---



这不是正确的查询，但我尝试过这样的事情



声明@PSL_COUNT int = 1.6

选择CASE，当@ PSL_COUNT为空或@PSL_COUNT NOT喜欢'％[^ 0-9]％'然后0结束72结束



@PSL_COUNT int = 1.6无效int

@PSL_COUNT int = 1 ---- 100 ---- n有效int

@PSL_COUNT int =''//空白数据无效int





我想检查@PSL_COUNT是否有效，然后返回0，否则返回72（验证ID）



什么我试过了：



声明@PSL_COUNT int = 1.6

选择CASE WHEN @PSL_COUNT为空或@PSL_COUNT不喜欢'％[^ 0-9]％'那么0 ELSE 72结束

I have table in which a column allow either null value or integer value i want to check this using case statement in sql like below---

this not correct query but I have tried something like this

declare @PSL_COUNT int=1.6
select CASE WHEN @PSL_COUNT IS NULL OR @PSL_COUNT NOT LIKE '%[^0-9]%' THEN 0 ELSE 72 END

@PSL_COUNT int=1.6 not valid int
@PSL_COUNT int=1----100----n valid int
@PSL_COUNT int='' // blank data not valid int


I want to check if @PSL_COUNT is valid int then return 0 else return 72 (validation id)

What I have tried:

declare @PSL_COUNT int=1.6
select CASE WHEN @PSL_COUNT IS NULL OR @PSL_COUNT NOT LIKE '%[^0-9]%' THEN 0 ELSE 72 END

推荐答案

这么多错误......



INT字段不符合浮点值：1.6是浮点值，因此它将被截断为1



LIKE用于字符串，而不是数字。



INT（和FLOAT）值不能包含非数字字符，因此尝试检查它们是行不通的...



所以NULLABLE INT值总是NULL或有效整数 - 它不能是其他任何东西，所以检查它真的没有意义......

So many mistakes...

INT fields do not contain floating point values: 1.6 is a floating point value, so it will be truncated to 1

LIKE is for strings, not numbers.

INT (and FLOAT) values cannot contain non-numeric characters, so trying to check for them is not going to work...

So a NULLABLE INT value will always be either NULL or a valid integer - it can't be anything else, so there really is no point in checking for it...

你可以利用Sql Server中的 TRY_CONVERT 方法来检查一个类型是否 CONVERTible 到另一个类型



MS Docs：TRY_CONVERT [ ^ ]



基于您和他们的示例代码

You can utilize the TRY_CONVERT method in Sql Server to check if one type is CONVERTible to another

MS Docs: TRY_CONVERT[^]

Based on both your and their sample code

DECLARE @PSL_COUNT NVARCHAR(16) = '1.6'

SELECT
  Result = CASE
    WHEN TRY_CONVERT(INT , @PSL_COUNT) IS NULL   THEN 0
    ELSE                                              72
  END

我用NULL，1,1.0和&值检查了这个值。 1.6。

I checked this with values of NULL, 1, 1.0, & 1.6.

Test  Result
----  ------
NULL  0
1     72
1.0   0
1.6   0
abcd  0

这篇关于验证sqll服务器中的整数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！