问题描述
这是我认为一个简单的问题，但还没有得到解决方案.我想仅从此处说明的列中获取有效数字.
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.
假设我们有一个具有以下值的 varchar 列
Lets say we have a varchar column with following values
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]%' 本来可以，但它会返回
select * from tbl where answer like '%[0-9]%' would have done it but it returns
234.62
2:234:43:22
6435.23
2
显然，这里不需要 2:234:43:22，因为它不是有效数字.
Here, obviously, 2:234:43:22 is not desired as it is not valid number.
想要的结果是
234.62
6435.23
2
有没有办法做到这一点?
Is there a way to do this?
推荐答案
你可以试试这个
ISNUMERIC (Transact-SQL)
ISNUMERIC 在输入时返回 1表达式计算为有效数字数据类型；否则它返回 0.
ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 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
这篇关于sql like 运算符仅获取数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！