I want to export data from oracle database to csv, and I am putting a number filter on a varchar column. It thows ORA:01722 error. Please suggest

Question

Select count(*) from table where loc between 300 to 400. 

1. loc is a varchar column.
2. it is not selecting all the data
3. checking the count, gives ORA :01722 error
4. exporting the results with error.

Edit from comment:

loc contains values less than 300, more than 400, and alphanumeric like 'GT' , '3KT1'

Answer 1

Your current query is trying to compare a varchar to a number. So it tries to convert the varchar to a number on the fly. This is called implicit conversion.

You should make it compare a varchar to a varchar.

Use single quotes so that you are comparing to varchars, not numbers

Select count(*) from table where loc between '300' to '400'

Then go and read about implicit conversion

Based on the update to your question, this column is a legitimate varchar and should not be converted to a numeric data type.

However you do need to work out whether you are incorrectly storing different types of data in the same column

Answer 2

loc is a varchar column.
[From comment] The Loc column has char type value also like GJ, 3KT1

LOC contains values which are not convertible to numbers. This matters because your WHERE clause predicates are defined as numbers, so Oracle applies an implicit to_number(loc) to the query. This is why using proper data types is best practice: it doesn't help you now but please learn the lesson, and use NUMBER columns for numeric data.

In the meantime you have several options to deal with your shonky data model.

If you're lucky enough to be using Oracle 12c R2 you can use the new VALIDATE_CONVERSION() function to exclude values of loc which can't be cast to numbers. Find out more

If you're using an earlier version of Oracle you can build your own function:

create or replace function is_number
    (p_str in varchar2) return number 
is 
    n number;
    rv number;
begin 
    begin
        n := to_number(p_str);
        rv := 1;
    exception
        when invalid_number then
             rv := 0;
    end;
    return rv;
end;

The weakest option would be casting the predicates to strings. where loc between '300' to '400' would include '3000', '4' and various other values you probably don't want.

Here is a LiveSQL demo (free Oracle Technet account required, alas).