CODEWITHSUNDEEP
oracle-database
Channaka Nilendra
Channaka Nilendra

Reputation: 11

Indexing very long number column

I have a table with few columns including 2 varchar2(200) columns. In this columns we basically store serial numbers which can be numeric or alpha-numeric. Alpha-numeric values always both serials are same in those 2 columns. However for number serials it is a range like (first column value = 511368000004001226 and second column value = 511368000004001425 with 200 different (Qty)). Maximum length of the serial is 20 digits. I have indexed both the columns. Now I want to sear a serial in-between the above range. (lets say 511368000004001227). I use following query.

SELECT   *
  FROM   Table_Namr d
WHERE   d.FROM_SN <= '511368000004001227'
         AND d.TO_SN >= '511368000004001227'

Is it a valid query? Can I use <=> operators for numbers in a varchar column?

Upvotes: 1

Views: 84

Answers (3)

Marmite Bomber
Marmite Bomber

Reputation: 21085

You may use alphanumerical comparison provided

1) your ranges are of the same length and

2) all the keys in the range are of the same length

Example data

SERNO                                  
----------------------------------------
101                                      
1011                                     
1012                                     
1013                                     
1014                                     
102                                      
103                                      
104

This doesn't work 

select * from tab
where serno >= '101' and serno <= '102';

SERNO                                  
----------------------------------------
101                                      
102                                      
1011                                     
1012                                     
1013                                     
1014

But constraining the lentgh of the result provides the right answer

select * from tab
where serno >= '101' and serno <= '102'
and length(serno) = 3;

SERNO                                  
----------------------------------------
101                                      
102

Upvotes: 1

Christian
Christian

Reputation: 36

Your Query is "valid" in the sense, that it works, and will deliver a result. If you are looking from a numeric standpoint, it will not work correctly, as the range operators for VARCHAR columns work the same way, as it would sort an alphanumeric value.

e.g.

d.FROM_SN >= '51000'
         AND d.TO_SN <= '52000'

This would match for values, as you would expect, like 51001, 51700, but would also deliver unexpected values like 52, or 5100000000000000

If you want numeric selection, you would need to parse it - which of course only works, if every value in these columns is numeric:

TO_NUMBER(d.FROM_SN) >= 51000
         AND TO_NUMBER(d.TO_SN) <= 52000

Upvotes: 1

Popeye
Popeye

Reputation: 35920

Yes, You can use >= and <= operators on Varchar2 columns but it will behave like it is string and comparison between strings will take place.

In this case, 4 will be considered greater than 34 means '4' > '34' but number 4 is less than 34.

It is not a good practice to store a number in Varchar2. You will lose the functionality of Numbers if you store them in varchar2.

You can check the above concept using following:

select * from dual where '4' > '34'; -- gives result 'X'
select * from dual where 4 > 34; -- Gives no result

You can try to convert the varchar2 column to number using to_number if possible in your case.

Cheers!!

Upvotes: 1

Related Questions