How to use between condition with numbers for string columns in Oracle

Question

I need to check the between condition for two numbers in the tables but the column in oracle DB has string values .

I tried the following query , nothing helped me

select * from  sys.employee_infor where  to_number(emp_number) between 1200 and 2400;

select * from  sys.employee_infor where (emp_number) >= to_char(1200) and (emp_number)  <= to_char(2400);

select * from  sys.employee_infor where  to_number(emp_number) between '1200' and '2400';

Received error as :

ORA-01722: invalid number

My Emp_number column be like as,

Could you provide some sample data? why will you use string type store number value in emp_number column? — D-Shih
– D-Shih, Commented Jan 22, 2019 at 8:07
Consider not using SYS schema for your own data. SYS (and SYSTEM) are special, leave them alone. If you do something unexpected, you might destroy the database. Create another user and do whatever you're doing there. — Littlefoot
– Littlefoot, Commented Jan 22, 2019 at 8:12
NEVER, ever create your own tables in the SYS or SYSTEM schema. Create a regular user and create your tables there. Do NOT use SYS or SYSTEM for your application ar anything else than doing DBA work. Just don't — user330315
– user330315, Commented Jan 22, 2019 at 8:53

Salman Arshad · Accepted Answer · 2019-01-22 08:37:44Z

2

The safer solution is to use CASE:

SELECT *
FROM sys.employee_infor
WHERE CASE WHEN NOT REGEXP_LIKE(emp_number, '\D') THEN TO_NUMBER(emp_number) END BETWEEN 1200 AND 2400

answered Jan 22, 2019 at 8:37

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

madhan Over a year ago

REGEXP_LIKE is taking long time to run the query , I need to include this sub query into the main query and Sys is just a example one the user name is different from sys.Please help me to fix the issue

Salman Arshad Over a year ago

The correct solution is to store numbers as numbers. Rest are just workarounds. You could try using a more efficient regex e.g. CASE WHEN REGEXP_LIKE(emp_number, '^\d{1,10}$') THEN TO_NUMBER... or add another filter e.g. WHERE emp_number BETWEEN '0' AND '9' AND CASE .... Or use the on conversion error solution in the other answer.

GMB · Accepted Answer · 2019-01-22 08:48:59Z

It is likely that you have data in the employee number column that cannot be converted to a number.

Assuming that the employee number is an integer value, the following query will show you the offending rows :

select * from sys.employee_infor where regexp_like(emp_number, '[^0-9]');

You can use that where clause in your query to ignore badly formatted data :

select * from (
    select * from sys.employee_infor where not regexp_like(emp_number, '[^0-9]')
) where  to_number(emp_number) between 1200 and 2400

PS : as you are looking to compare numbers, not strings.

Another solution is to use the DEFAULT ... ON CONVERSION ERROR option of function TO_NUMBER(), which is available starting Oracle 12c R2. With this option conversion errors are trapped and a default value is returned instead of throwing an error :

select * 
from sys.employee_infor 
where to_number(emp_number default 0 on conversion error) between 1200 and 2400

Gordon Linoff · Accepted Answer · 2019-01-22 12:09:39Z

0

Under many circumstances, this will work:

where emp_number >= '1200' and emp_number <= '2400'

or:

where emp_number >= '1200' and emp_number <= '2400' and
      length(emp_number) = 4

This is not exactly the same, because it is using string comparisons and not numeric comparisons. On the other hand, it can take advantage of indexes.

answered Jan 22, 2019 at 12:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

madhan Over a year ago

Thanks for the suggestion , But when I use the single condition it returns the value but using the two condition nothing was returned . Also What if the comparison between '100' and '1000'

Collectives™ on Stack Overflow

How to use between condition with numbers for string columns in Oracle

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related