phone

Using Between Clause To Compare in Oracle

Image showing that Dimension Systems is a Oracle PeopleSoft partner.

Using between clause to compare alphanumeric values in Oracle

We use between clause in a SQL statement to see if a value falls within a range. This works fine for numbers, and characters. But, if you have both numbers, and characters in a string then you will see unexpected results.

Let us say we have a table with ranges.

A1 to A99 is range 1.

A100 to A199 is range 2,

A200 to A299 is range 3.

CREATE TABLE RANGES (FROM_VALUE VARCHAR2(10), TO_VALUE VARCHAR2(10), RANGE NUMBER(1));

INSERT INTO RANGES VALUES (‘A1′, ‘A99′, 1);

INSERT INTO RANGES VALUES (‘A100′, ‘A199′, 2);

INSERT INTO RANGES VALUES (‘A200′, ‘A299′, 3);

Now, when we run the below SQL

SELECT RANGE FROM RANGES WHERE ‘A100′ BETWEEN FROM_VALUE AND TO_VALUE; We expect it to return range 2. However, it returns two rows – 1, and 2. This happens because strings are compared character by character, by converting the characters into their equivalent ASCII values. So, in this case, when A100 is compared to A99, it believes A100 is lesser than A99 as A1 is lesser than A9.

To avoid this issue, you should separate characters from numbers in alphanumeric strings and compare them individually.

We can use regular expressions to accomplish this.