ISNumeric Alternatives for Oracle

Asked by At

I want to see what value is higher A or B. They are strings but most strings only contain Numbers. i need to convert those numbers so i can use the > and < operators.

They don't want me to create a function on "their" database. So that's not an option. I tried Lukasz Szozda his approach on "Safe" TO_NUMBER() but that just gives me "Missing Right Parenthesis"

I tried https://docs.oracle.com/cd/E51367_01/financialsop_gs/FADRA/ch09s05s53.html No succes either.

And i tried with REGEX but no luck either.

SELECT *
FROM Table
WHERE 
(
 TO_NUMBER('T' DEFAULT 0 ON CONVERSION ERROR) 
 > 
 TO_NUMBER('5' DEFAULT 0 ON CONVERSION ERROR)
)

SELECT *
FROM Table
WHERE 
(
 TO_NUMBER('7' DEFAULT 0 ON CONVERSION ERROR) 
 > 
 TO_NUMBER('5' DEFAULT 0 ON CONVERSION ERROR)
)

The first bit of code should return nothing, the second the entire table. But all i get is "Missing Right Parenthesis"

1 Answers

3
Alex Poole On Best Solutions

As noted in Lukasz's answer on the question you linked to, the on conversion error syntax was added in 12cR2, so you cannot use it in 11gR2 - that is the cause of the error you are getting.

The regular expression method described in that question works though:

SELECT *
FROM dual
WHERE 
(
 COALESCE(TO_NUMBER(REGEXP_SUBSTR('T', '^\d+')), 0) 
 > 
 COALESCE(TO_NUMBER(REGEXP_SUBSTR('5', '^\d+')), 0)
);

no rows selected

SELECT *
FROM dual
WHERE 
(
 COALESCE(TO_NUMBER(REGEXP_SUBSTR('7', '^\d+')), 0)
 > 
 COALESCE(TO_NUMBER(REGEXP_SUBSTR('5', '^\d+')), 0)
);

D
-
X