sql like operator to get the numbers only

Question

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?

Answer 1

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 

Answer 2

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