Query for handling valid numeric

Question

How to get valid value from the following query

SELECT Answer FROM table 
WHERE values LIKE '%[^0-9]%'

Basically I want the data can deal for

1. 28,000 (valid)
2. $20000 (valid)
3. Annual Amount (invalid)
4. ? (invalid)
5. 28.00 (valid)

Thanks

Answer 1

you could do something like:

select replace(replace(values, '$', ''), ',', '') as number from table
  where dbo.RegexMatch(values, ^\$?(\d+|(\d{1,3}(,\d{3})+))(\.\d+)?$')

tweak the regex to match any conditions you need...

Answer 2

SELECT Answer
FROM table
WHERE 
    ISNUMERIC(values)
    OR (
        SUBSTRING(values, 1, 1) = '$' 
        AND ISNUMERIC(RIGHT(values, LEN(values) - 1)))