Oracle / PLSQL: Sort a varchar2 field as a numeric field

Question: I have a field defined in Oracle as a varchar2, but it contains numbers. When I use an "order by" clause, the records are sorted ascending by character. But I want to sort it numerically without changing the datatype from varchar2 to numeric. Is there any solution?

Answer: To sort your field numerically, there are two possible solutions:

Solution #1

This first solution only works if the varchar2 field contains all numbers. To do this, you will need to add another field to your "order by" clause that evaluates to a number, but you don't have to include this new field in your SELECT portion of the SQL.

For example,

You may have a supplier table defined as:

create table supplier
( supplier_id varchar2(10) not null,
  supplier_name varchar2(60)
);

When you perform the following select statement, the supplier_id field sorts alphabetically, even though it contains numbers.

select * from supplier
order by supplier_id;

However, you could execute the following SQL, which will return a numerically sorted supplier_id:

select * from supplier
order by TO_NUMBER(supplier_id);

This SQL converts the supplier_id field to a numeric value and then sorts the value in ascending order. This solution returns an error if not all of the values in the supplier_id field are numeric.

Solution #2 (more eloquent solution)

We'd like to thank Kamil for suggesting this solution.

This solution will work even if the varchar2 field contains non-numeric values.

Again, we'll demonstrate this solution on the supplier table, defined as:

create table supplier
( supplier_id varchar2(10) not null,
  supplier_name varchar2(60)
);

Remember that our goal is to sort the supplier_id field in ascending order (based on its numeric value). To do this, try using the LPAD function.

For example:

select * from supplier
order by LPAD(supplier_id, 10);

This SQL pads the front of the supplier_id field with spaces up to 10 characters. Now, your results should be sorted numerically in ascending order.

Please note that if your numbers in the supplier_id field are longer than 10 digits, you may need to increase the second parameter on the LPAD function.

Learn more about the LPAD function.

 

Oracle / PLSQL: Test a string for a numeric value

Question: In Oracle, I want to know if a string value is numeric only. How can I do this?

Answer: To test a string for numeric characters, you could use a combination of the LENGTH function, TRIM function, and TRANSLATE function built into Oracle.

You can use the following command:

LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' ')))

string1

The string value that you are testing.

This solution uses the TRANSLATE, LENGTH, and TRIM functions to test a string for a numeric value if the numeric value is properly formatted. It will return a null value if string1 is numeric. It will return a value "greater than 0" if string1 contains any non-numeric characters.

For example,

SELECT LENGTH(TRIM(TRANSLATE('123b', ' +-.0123456789',' '))) FROM dual;
Result: 1

SELECT LENGTH(TRIM(TRANSLATE('a123b', ' +-.0123456789',' '))) FROM dual;
Result: 2

SELECT LENGTH(TRIM(TRANSLATE('1256.54', ' +-.0123456789',' '))) FROM dual;
Result: null

SELECT LENGTH(TRIM(TRANSLATE ('-56', ' +-.0123456789',' '))) FROM dual;
Result: null

Although this first method will test a string if it is a properly formatted number, it would allow a number to pass such as -+1..8++-, which is of course not a number. A better way to test a string for a numeric value is to create your own function that uses the TO_NUMBER function to test the string value.

For example:

CREATE FUNCTION is_number (p_string IN VARCHAR2)
   RETURN INT
IS
   v_new_num NUMBER;
BEGIN
   v_new_num := TO_NUMBER(p_string);
   RETURN 1;
EXCEPTION
WHEN VALUE_ERROR THEN
   RETURN 0;
END is_number;

This new function called is_number would return 1 if the value is numeric and 0 if the value is NOT numeric. You could execute the is_number function as follows:

SELECT is_number('123') FROM dual;
Result: 1

SELECT is_number('123b') FROM dual;
Result: 0

SELECT is_number('-+1..8++-') FROM dual;
Result: 0

So this method would catch the fact that -+1..8++- is not a valid number.