Firebird SQL order string numerically

Question

I have a field named SERIAL (varchar 25) and we store data like: 1, 2, w1, w100, w2, z1, z0, 005, 02... and need order like numbers. Using order by does not work, using char_length() too does not work, because the size is variable. How can I order it?

I expect this output: 005, 02, 1, 2, w1, w2, w100, z0, z1.

Can you describe what your ordering rule should be? I see your example but I don't decipher a pattern. — Sam M, Nov 10 '22 at 23:49
Create isNumeric function. `Order by NOT isnumeric(serial), serial` See: https://www.firebirdfaq.org/faq139/ and https://stackoverflow.com/questions/66660667/check-for-is-numeric-in-firebird-database I think the pattern is you want all string values which are all numeric first, and those with mixed characters second. but yet your still storing the numeric values as if they were strings — xQbert, Nov 11 '22 at 01:31
Why would 005 be sorted before 02 and 02 before 1? For normal numeric-sort algorithms, that order doesn't make sense, as they will basically ignore leading zeros. — Mark Rotteveel, Nov 11 '22 at 09:59
@Sam M, this field is used for product's serial numbers and the user insert any string (numbers or strings+numbers). Tks. — Marcio, Nov 12 '22 at 01:32
so the short answer is check to see if value is numeric if it is assign 0 else 1. Then sort by that result then by the field itself — xQbert, Nov 18 '22 at 15:46

Mark Rotteveel · Answer 1 · 2022-11-11T09:58:58.573

The default collations provided by Firebird do not apply this form of sorting (or at least, I can't think of any that do). You will need to create a custom collation for this to work, and you will have to use the UTF8 character set for the column involved.

To be clear, the solution below does not offer the exact sort order you describe, instead it will sort as: 1, 02, 2, 005, w1, w2, w100, z0, z1. I'm not aware of numeric sorting algorithms that would treat leading zeros differently than other numbers.

First of all, create a custom collation:

create collation unicode_numeric for utf8 from unicode 'NUMERIC-SORT=1';

This creates a derivation of the built-in UNICODE collation, with the NUMERIC-SORT enabled. You could also use another UTF8 collation as a base (e.g. UNICODE_CI or UNICODE_CI_AI, etc.).

If the column you're sorting on is already UTF8, you can sort by applying the collate clause:

select val 
from numeric_sort_example2 
order by val collate unicode_numeric;

If the column has a different character set, you will first have to cast before you can apply the collation:

select val 
from numeric_sort_example3 
order by cast(val as varchar(50) character set utf8) collate unicode_numeric;

You can also specify the collation as part of the column definition:

create table numeric_sort_example1 (
  val varchar(50) character set utf8 collate unicode_numeric
);

You can then use order by without an explicit collation:

select val 
from numeric_sort_example1 
order by val;

See also this fiddle: https://dbfiddle.uk/UnePdleL

Firebird SQL order string numerically

1 Answers1