CODEWITHSUNDEEP
oracle-sqldeveloper
dharan
dharan

Reputation: 753

Comparing values with VARCHAR2 in oracle sql developer

I am trying a very simple query which returns the results which are less than some particular value. I am using the below query

SELECT
    *
FROM
    ok_dc.gms3_vc_mme_vin_si_detail
WHERE
    vc_vin_locale = 'en_EU'
    AND vc_vin_document_id = 'SI120804'
    AND vc_vin_vis_start_range <= '670702';

However, as shown below the data returned is not accurate. The data type of VC_VIN_VIS_START_RANGE is VARCHAR2. I read Oracle documentation, it was said that automatic conversion will be made while doing comparisons.

I need help with understanding what is wrong with the query or other possible solutions for handling this case.

Appreciate any help!

enter image description here

Upvotes: 0

Views: 5023

Answers (1)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30565

Varchar (string) comparison is different then normal numeric comparison.

'1', '10', '11', '2', '20' => this is sorted string (varchar)
1, 2, 10, 11, 20 => this is sorted number

hence, you need to convert varchar2 to number when comparing

TO_NUMBER(VC_VIN_VIS_START_RANGE) < 670702

Please note that you may need functional index for TO_NUMBER(VC_VIN_VIS_START_RANGE)

Upvotes: 2

Related Questions