SQL Server在转换前将字符串转换为整数检查值 [英] SQL server casting string to integer checking value before casting

问题描述

我有一个表，其中的字段名为MINIMUM_AGE.存储在此字段中的值的类型为nvarchar:

I have a table with a field named MINIMUM_AGE. The values stored in this field are of type nvarchar:

17 years
54 years 
N/A
65 years

我想在该列上应用WHERE子句以检查特定的年龄范围.为此，我需要从字段值中解析出年龄.

I would like to apply a WHERE clause on the column to check for a certain age range. To do that I need to parse out the age from the field values.

所以，我想我需要选择前两个字符，然后将它们转换为整数.此外，某些字段可能不包含前两个字符的数字.有些可能只是N/A.因此，我需要在投放之前进行检查.

So, I think I need to select the first two characters, then cast them into an integer. Also, some fields may not contain numbers for the first two characters. Some may simply be N/A. So, I will need to check for that before casting.

有人可以解释如何做到这一点吗?

Can someone explain how to accomplish this?

推荐答案

这是 SQL小提琴 ，它演示以下查询:

Here is the SQL Fiddle that demonstrates the below query:

SELECT CASE 
  WHEN MINIMUM_AGE <> 'N/A' 
  THEN CAST(LEFT(MINIMUM_AGE, 2) AS int)
  ELSE 0
  END
FROM MyTable

注意:CASE表达式只能返回一种数据类型.因此，在上面的示例中，如果MINIMUM_AGE是N/A，则它将返回0.

Note: the CASE expression can only return one data type. So, in the example above if the MINIMUM_AGE is N/A then it returns 0.

如果您希望它返回 null ，请使用以下命令:

If you would rather have it return null, then use the following:

SELECT CASE 
  WHEN MINIMUM_AGE <> 'N/A' 
  THEN CAST(LEFT(MINIMUM_AGE, 2) AS int)
  END
FROM MyTable

这篇关于SQL Server在转换前将字符串转换为整数检查值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！