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
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
234.62
2:234:43:22
6435.23
2
Here, obviously, 2:234:43:22 is not desired as it is not valid number.
The desired result is
234.62
6435.23
2
Is there a way to do this?
This question is related to
sql
sql-server
numbers
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]%'
You can use the following to only include valid characters:
SQL
SELECT * FROM @Table
WHERE Col NOT LIKE '%[^0-9.]%'
Results
Col
---------
234.62
6435.23
2
what might get you where you want in plain SQL92:
select * from tbl where lower(answer) = upper(answer)
or, if you also want to be robust for leading/trailing spaces:
select * from tbl where lower(answer) = trim(upper(answer))
With SQL 2012 and later, you could use TRY_CAST
/TRY_CONVERT
to 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)
Source: Stackoverflow.com