CODEWITHSUNDEEP
t-sql
dcpartners
dcpartners

Reputation: 5446

Query for handling valid numeric

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

Upvotes: 1

Views: 77

Answers (2)

jspcal
jspcal

Reputation: 51904

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...

Upvotes: 1

Jacob
Jacob

Reputation: 78840

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

Upvotes: 1

Related Questions