rwur rwur - 1 month ago 12
SQL Question

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

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

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;

Comments