For an employee table i need to sort the list by the employeenumber (char). The input for this field is a string that can include any combination of alphanumerical and special characters. The desired output order is resembling Natural ordering.
Examples of common input:
- Numbers only: {2,4,10,20,400,403,405} -> should be sorted as numbers
- Alphanumeric: {A20,A30,B10,C30,C300} -> should be sorted alphabetically
- Numbers with special chars: {4,4-1,10,15,15-2,17,20}
- Control sample {B-33,-14,17B,A18,#62,A 76,C25,3-34,1,3,10,33,45,101,302}
I already have this working with MySql adding the following to the order by:
CAST(employeenumber AS SIGNED), employeenumber
For postgresql I have tried the following that works if the input only contains integers:
CASE
WHEN employeenumber~E'^[\\d\\s]+$'
THEN CAST (employeenumber AS INTEGER)
ELSE 0
END
Working mysql example:
SELECT
employeenumber
FROM
employee
ORDER BY
CAST(employeenumber AS SIGNED), employeenumber;
- Input: {1,2,30,12b}
- Output: {1,2,12b,30}
So for postgresql the I'm currently working with 2 options:
Query 1(Q1):
SELECT employeenumber FROM employee ORDER BY employeenumber;
Query 2(Q2):
SELECT employeenumber FROM employee ORDER BY CASE WHEN employeenumber~E'^[\\d\\s]+$' THEN CAST (employeenumber AS INTEGER) ELSE 0 END
- Input: {1,2,30,12b}
- Expected/preferred output: {1,2,12b,30} or {1,2,30,12b}
- Output Q1: {1,12b,2,30}
- Output Q2: {12b,1,2,30}
I have been able to get acceptable results if the input set is restricted to fx numbers only or alphanumeric, but as soon as special characters are introduced or if the Q2 is used on an alphanumeric set it gives problems.
Running Q2 on the Control sample gives the following result:
{B-33,-14,17B,A18,C25,#62,A 76,3-34,1,3,10,33,45,101,302}
The numerical part is ordered as expected, but the alphanumeric/special char part is all kinds of jumbled.
