Ordering streets by their names and polysyllabic numbers in SQL query

Question

In Oracle DB there is a table with some data:

LAG	HNR	STREET
00020	44	Aachener Straße
00020	44/2	Aachener Straße
00020	44/1	Aachener Straße
00020	46	Aachener Straße
00020	46/6	Aachener Straße
00020	46/5	Aachener Straße
00020	46/3	Aachener Straße
00020	46/1	Aachener Straße
00020	46/2	Aachener Straße
00020	47	Aachener Straße
00020	48	Aachener Straße
00020	48/1	Aachener Straße
00020	48/2	Aachener Straße
00020	48A	Aachener Straße

Please find a minimal reproducible example here: http://www.sqlfiddle.com/#!4/19d625/1

Now I am trying to sort this data via the "HNR" in the order of 25 > 25A > 25B > 25/3 . So, the highest priority has a simple number, then goes the same number with letters (also sorted), and lastly goes the same number with a sub-number after / (sorted).

Using this code

SELECT F.LAG,
       F.HNR,
       F.STREET
FROM DB.F F
ORDER BY F.LAG,
         F.STREET,
         TO_NUMBER(regexp_substr(F.HNR, '[[:digit:]]{1,4}')),
         TO_CHAR(regexp_substr(F.HNR, '[[:alpha:]]{1}')),
         TO_NUMBER(regexp_substr(F.HNR, '[^/]+', 1, 2));

I am getting this:

LAG	HNR	STREET
00020	44/1	Aachener Straße
00020	44/2	Aachener Straße
00020	44	Aachener Straße
00020	46/1	Aachener Straße
00020	46/2	Aachener Straße
00020	46/3	Aachener Straße
00020	46/5	Aachener Straße
00020	46/6	Aachener Straße
00020	46	Aachener Straße
00020	47	Aachener Straße
00020	48/1	Aachener Straße
00020	48/2	Aachener Straße
00020	48A	Aachener Straße
00020	48	Aachener Straße

Instead of this:

LAG	HNR	STREET
00020	44	Aachener Straße
00020	44/1	Aachener Straße
00020	44/2	Aachener Straße
00020	46	Aachener Straße
00020	46/1	Aachener Straße
00020	46/2	Aachener Straße
00020	46/3	Aachener Straße
00020	46/5	Aachener Straße
00020	46/6	Aachener Straße
00020	47	Aachener Straße
00020	48	Aachener Straße
00020	48A	Aachener Straße
00020	48/1	Aachener Straße
00020	48/2	Aachener Straße

What am I doing wrong?

I have seen SQL: ORDER BY using a substring within a specific column… possible? and Order by alphabet and then by numbers, but I did not find there anything relevant.

Answer 1

I would separately introduce sorting based on the presence of special characters in HNR, in addition to sorting on the bits related to those characters that you have already implemented.

You could try using REGEXP_COUNT for this. Here is how you could go about it:

SELECT F.LAG,
       F.HNR,
       F.STREET
FROM DB.F F
ORDER BY F.LAG,
         F.STREET,
         TO_NUMBER(regexp_substr(F.HNR, '[[:digit:]]{1,4}')),
         regexp_count(F.HNR, '[/]'),  -- numbers with strokes follow those without strokes
         regexp_count(F.HNR, '[[:alpha:]]'),  -- no-letter numbers first
         TO_CHAR(regexp_substr(F.HNR, '[[:alpha:]]{1}')),
         TO_NUMBER(regexp_substr(F.HNR, '[^/]+', 1, 2));

The two additional sorting criteria I added above will result in the following:

HNR	regexp_count(HNR, '[/]')	regexp_count(HNR, '[[:alpha:]]')
48	0	0
48A	0	1
48/1	1	0

As you can see, using them in that specific order will produce the desired sorting order for HNR.

See live demo of this solution at SQL Fiddle, at db<>fiddle.

Answer 2

PostgreSQL

With PostgreSQL you can do this,

SELECT x
FROM ( VALUES ('25'), ('25A'), ('25B'), ('25/3') ) AS t(x)
CROSS JOIN LATERAL regexp_matches(x, '(\d+)(?:(\w+)|(?:\/(\d+)))?')
  AS re(m)
ORDER BY m[1], m[3] NULLS FIRST, m[2] NULLS FIRST;

The key here is

CROSS JOIN LATERAL regexp_matches(x, '(\d+)(?:(\w+)|(?:\/(\d+)))?')
  AS re(m)

That returns an ARRAY with your matches,

  x   |       m        
------+----------------
 25   | {25,NULL,NULL}
 25A  | {25,A,NULL}
 25B  | {25,B,NULL}
 25/3 | {25,NULL,3}

We just want to order

• by the numbers first so that's m[1]
• by the third column which is null in { 25, 25A, 25B }. So bump those to the top, but for the 25/3 and 25/4 order ASC (the default)
• and then lastly, order by the second column in ASC order with NULLs at the top.

Answer 3

Does this do it? (I generated my own test data to make it easier to confirm that '4' comes before '44' etc.)

with street_numbers (hnr) as
     ( select n1.column_value||n2.column_value
       from   table(sys.ku$_vcnt('4','40','44')) n1
              cross join table(sys.ku$_vcnt('', 'A','B','/1','/11')) n2 )
select hnr
from   street_numbers
order by
       regexp_substr(hnr,'^\d+')
     , replace(hnr,'/',chr(123));

which gives:

HNR
-----
4
4A
4B
4/1
4/11
40
40A
40B
40/1
40/11
44
44A
44B
44/1
44/11

The idea is to sort by the leading numeric first, so 4 comes before 5 etc, then by the whole string but changing any / to an arbitrary character above z.