sql like operator to get the numbers only
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
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 try this
ISNUMERIC (Transact-SQL)
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