Oracle: to_number() combined with substr() and regexp_like() in WHERE clause

Question

I have a table with a column "description" which has the following values:

• OPTestMachine
• OPManualTesting
• OP1010

So the select statement, to get the values, would just be

SELECT description
  FROM operation;

I want to extract the number "1010" (or any string which matches the substr() criterion) and convert the "found string" into an integer if possible.

So I came up with this:

SELECT to_number(substr(description, 3, 4))
  FROM operation
 WHERE regexp_like(substr(description, 3, 4), '^\d+(\.\d+)?$', '')

The result is plain and simple: "1010"

That works pretty well for me.

Now the hard part for me: I want to use the substr()-result in the WHERE-clause

Something like this:

 SELECT to_number(substr(description, 3, 4))
   FROM operation
  WHERE regexp_like(substr(description, 3, 4), '^\d+(\.\d+)?$', '')
    AND substr(description, 3, 4) < 2000;

When I do this I get the error "Invalid number". I guess it is because of the order how the server is parsing the select statement.

If you could provide any help that'd be great!!

Answer 1

The substr function returns a string, and you have to explicitly cast it to number as you did in the select statement: AND to_number(substr(description, 3, 4)) < 2000;