In Oracle, I have a table with a column (X) which can have strings like this:
97M
481
101X
88
21E
etc.
I want to select just those rows where the integral value of x > 90. In this example, I would expect to get back the rows containing values 97M, 101X and 481. How can I do this?
I used REGEXP_REPLACE to remove the alpha characters before using TO_NUMBER so I could filter the results as needed:
WITH t
AS (SELECT '97F' AS x FROM DUAL
UNION
SELECT '481' FROM dual
UNION
SELECT '101A' FROM dual
UNION
SELECT '101A' FROM dual
UNION
SELECT '88' FROM dual
UNION
SELECT '21E' FROM dual)
SELECT x
FROM t
WHERE TO_NUMBER(regexp_replace(x, '[[:alpha:]]', '')) > 90;
X
101A
481
97F
Hope it helps...
45A2, i suggest to_number(regexp_substr(x, '^\d*'))
You can always use translate to remove alpha characters.
TO_NUMBER(translate('90F', '1ABCDEFGHIJKLMNOPQRSTUFWXYZ', '1')) -- = 90
Translate does a 1 to 1 translation of the characters in the second argument to the characters in the third argument.
Here's a different example.
translate('ABCDEFG', 'ABC', 'XYZ') = 'XYZDEFG'
A -> X
B -> Y
C -> Z
Now if you look at my example
translate('90F', '1ABCDEFGHIJKLMNOPQRSTUFWXYZ', '1')
1 -> 1 (this is here because if that last argument is null, you'll get an empty string)
A -> ? there's nothing here, so oracle will translate it to nothing
B -> same as above
You might try the following:
WHERE (substr(x, 1, 1) = '9' and substr(x, 2, 1) between '1' and '9'
) or
(substr(x, 1, 1) between '1' and '9' and
substr(x, 2, 1) between '0' and '9' and
substr(x, 3, 1) between '0' and '9'
)
This is a bit brute force. It checks to see if the string starts with 91-99 or if the first three digits are a string.
