sql like 运算符只获取数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2032742/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql like operator to get the numbers only
提问by Thunder
This is I think a simple problem but not getting the solution yet. I would like to get the valid numbers only from a column as explained here.
这是我认为一个简单的问题，但还没有得到解决方案。我想仅从列中获取有效数字，如此处所述。
Lets say we have a varchar column with following values
假设我们有一个带有以下值的 varchar 列
ABC
Italy
Apple
234.62
2:234:43:22
France
6435.23
2
Lions
Here the problem is to select numbers only
这里的问题是只选择数字
select * from tbl where answer like '%[0-9]%'would have done it but it returns
select * from tbl where answer like '%[0-9]%'会这样做，但它会返回
234.62
2:234:43:22
6435.23
2
Here, obviously, 2:234:43:22 is not desired as it is not valid number.
在这里，显然， 2:234:43:22 是不需要的，因为它不是有效数字。
The desired result is
想要的结果是
234.62
6435.23
2
Is there a way to do this?
有没有办法做到这一点？
采纳答案by Adriaan Stander
You can try this
你可以试试这个
ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0.
当输入表达式计算为有效的数值数据类型时，ISNUMERIC 返回 1；否则返回 0。
DECLARE @Table TABLE(
Col VARCHAR(50)
)
INSERT INTO @Table SELECT 'ABC'
INSERT INTO @Table SELECT 'Italy'
INSERT INTO @Table SELECT 'Apple'
INSERT INTO @Table SELECT '234.62'
INSERT INTO @Table SELECT '2:234:43:22'
INSERT INTO @Table SELECT 'France'
INSERT INTO @Table SELECT '6435.23'
INSERT INTO @Table SELECT '2'
INSERT INTO @Table SELECT 'Lions'
SELECT *
FROM @Table
WHERE ISNUMERIC(Col) = 1
回答by beach
You can use the following to only include valid characters:
您可以使用以下内容仅包含有效字符：
SQL
SQL
SELECT * FROM @Table
WHERE Col NOT LIKE '%[^0-9.]%'
Results
结果
Col
---------
234.62
6435.23
2
回答by David Hall
Try something like this - it works for the cases you have mentioned.
尝试这样的事情 - 它适用于您提到的情况。
select * from tbl
where answer like '%[0-9]%'
and answer not like '%[:]%'
and answer not like '%[A-Z]%'
回答by Mark Sowul
With SQL 2012 and later, you could use TRY_CAST/TRY_CONVERTto try converting to a numeric type, e.g. TRY_CAST(answer AS float) IS NOT NULL-- note though that this will match scientific notation too (1+E34). (If you use decimal, then scientific notation won't match)
对于 SQL 2012 及更高版本，您可以使用TRY_CAST/TRY_CONVERT尝试转换为数字类型，例如TRY_CAST(answer AS float) IS NOT NULL-- 请注意，这也将匹配科学记数法 (1+E34)。（如果您使用decimal，则科学记数法将不匹配）
