Check for two decimal digit number in string

Question

I have two sets of records

Set 1:

-11
-12
-12 AN    
''    
-134
-125
+135

Set 2:

1.15
1.1

In Set 1 I need to check which values are either blank '' or start with a + sign and are greater than 125.

In Set 2 I need to check which values have less than two decimal places

Example output for the above sets:

''
+135
1.1

Answer 1

In SQL-Server could be something like that:

WITH cte AS (
SELECT Col
FROM set1
WHERE Col = '' OR Col LIKE'+%' AND (CAST(REPLACE(REPLACE(Col,'+',''),'-','') AS INT) > 125)
)
SELECT * FROM cte
UNION ALL
SELECT Col
FROM set2
WHERE Col LIKE '%._'

OUTPUT:

''  -- blank
+135
1.1

SQL FIDDLE

Answer 2

For the first set, you can use the like operator to check if a string starts with '+' and then cast it to numeric and compare it with 125. Using isnumeric beforehand will help avoid casting errors:

WHERE col = '' OR
      (col LIKE '+%' AND ISNUMERIC(col) AND CAST(col AS NUMERIC) > 125)

For the second set, you can use the like operator with _, the single character wildcard:

WHERE col NOT LIKE '%.__%'

Answer 3

One way:

where f = '' or (f like '+%' and isnumeric(f) = 1 and f > 0)

where isnumeric(f) = 1 and f like '%.[0-9]'

Answer 4

First:

WHERE v = '' OR (v NOT LIKE '%[^+0-9]%' AND v > 125)

Second:

WHERE v NOT LIKE '%[^.0-9]%' AND (v LIKE '%._' OR (v NOT LIKE '%.%' AND v LIKE '%_%'))

For decimals:

WHERE FLOOR(v*10) = v*10