2

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.

0

3 Answers 3

3

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.

0
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.
2
  • Thanks! Your answer looks promising and really advanced for me. I do not understand yet how to adapt it to real fields in my Oracle DB instead of VALUES.
    – Taras
    Jun 11, 2021 at 11:50
  • That's a nice feature. There's nothing like that in Oracle, though. Jun 20, 2021 at 12:00
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.