CODEWITHSUNDEEP
sqloracle-databasesubstrregexp-like
rwur
rwur

Reputation: 247

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:

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!!

Upvotes: 0

Views: 14085

Answers (2)

Sebz
Sebz

Reputation: 502

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;

Upvotes: 3

Kacper
Kacper

Reputation: 4818

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

Does second to_number help?

if not I would do:

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

Upvotes: 0

Related Questions