0

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

rwur
  • 237
  • 1
  • 6
  • 16

2 Answers2

3

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;

Sebz
  • 492
  • 2
  • 4
  • I'm sorry, but I have already tried this and it returns the same error "Invalid number" – rwur Oct 27 '16 at 14:02
  • 3
    My hunch here is that the substring returns something else than a number and it causes it to crash. Try trimming it in case it encounters spaces: `AND to_number(ltrim(rtrim(substr(description, 3, 4)))) < 2000;` – Sebz Oct 27 '16 at 14:06
  • yeah, you are right! The second solution with trim works! – rwur Oct 27 '16 at 14:08
  • @rwur trim() is good solution but I'm not sure if you can rely on order of condition checking. So maybe adding another query is safer. – Kacper Oct 27 '16 at 14:17
0
 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;
Kacper
  • 4,798
  • 2
  • 19
  • 34