0

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
    What Oracle version? – James May 28 '19 at 10:55
  • Oracle Database 11g Release 11.2.0.4.0 - 64bit –  May 28 '19 at 10:57
  • If they don't want you to create a function on "their" database, ask "them" to create it on your behalf. – Kaushik Nayak May 28 '19 at 10:59
  • 1
    The 'on conversion error' stuff was added in 12cR2, as Lukasz's answer said; you're getting the error because that isn't valid syntax in 11g. And 11g means you also can't use a local function defined in a CTE. The regex approach might work, but as you haven't shown your attempt or why it didn't work we can't help you fix that. – Alex Poole May 28 '19 at 11:00
  • @AlexPoole i feared as much... any option available? –  May 28 '19 at 11:01

1 Answers1

2

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
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • For some reason the semicolon gives an "Invalid Character"error but without it it seems to work for now. Gonna end the what more complex query i sue to work with this and see if it works :) Thank you. –  May 28 '19 at 11:19
  • 1
    The semicolon is a statement separator, I've got 20 years of muscle memory from working SQL\*Plus that makes me add it automatically. But yes, in another client, or with different settings, or over JDBC etc. - it isn't part of the statement so it will get that error. – Alex Poole May 28 '19 at 11:28
  • A bit of a follow up, What if i wanted to get the original value if it's not a number? Tried replacing the 0 with the variable used but get "inconsistent datatypes: expected NUMBER got CHAR" which is obvious. Any work around for this (not a big deal just a neat extra) because the only way i can think of is to somehow convert the number back to char after the comparison is done. –  May 28 '19 at 11:38
  • You are either comparing the values as numbers - in which case non-numeric values cannot be included; or you're treating everything as strings - which obviously won't sort numeric values in numeric order. You can't really mix them. How would you *want* to compare a 'number' and a string (like T and 5 I suppose)? You *could*, I suppose, normalise the numbers back to fixed-length zero-padded strings (if they are integers), but that doesn't sound good. I guess I don't really understand what you're trying to achieve. – Alex Poole May 28 '19 at 11:48