'Postgres order civic numbers by digits and letters

I've two columns in text format containing street names and civic numbers. I'd like to set a query to order rows by street name and then by digits and by characters (they can be uppercase or lowercase) from civ_text column

This is what I get if I order just by nome_via and by civ_text:

+──────────────────+──────────────────+
| nome_via (text)  | civ_text (text)  |
+──────────────────+──────────────────+
| ABRUZZI VIA      | 10               |
| ABRUZZI VIA      | 12               |
| ABRUZZI VIA      | 14               |
| ABRUZZI VIA      | 16               |
| ABRUZZI VIA      | 2                |
| ABRUZZI VIA      | 3                |
| ABRUZZI VIA      | 3A               |
| ABRUZZI VIA      | 3B               |
| ABRUZZI VIA      | 4                |
| ABRUZZI VIA      | 5                |
+──────────────────+──────────────────+

The following table shows my desired output:

+──────────────────+──────────────────+
| nome_via (text)  | civ_text (text)  |
+──────────────────+──────────────────+
| ABRUZZI VIA      | 2                |
| ABRUZZI VIA      | 3                |
| ABRUZZI VIA      | 3A               |
| ABRUZZI VIA      | 3B               |
| ABRUZZI VIA      | 4                |
| ABRUZZI VIA      | 5                |
| ABRUZZI VIA      | 10               |
| ABRUZZI VIA      | 12               |
| ABRUZZI VIA      | 14               |
| ABRUZZI VIA      | 16               |
+──────────────────+──────────────────+

I was thinking to use regular expressions but I was not able to do it. Can you help me to set the query to get results above ? Thanks in advance

postgresql


Solution 1:[1]

Query to extract leading number from civic numbers

select 
  civ_text, 
  (regexp_match(civ_text, '^(\d+)'))[1] civ_text_start_number
from (
  values ('1/A 34'), ('1/A 36'), ('15A'), ('A2'), ('3A'), ('2'), (null), ('A'), ('4')
) as a (civ_text) 
order by 
  coalesce((regexp_match(civ_text, '^(\d+)'))[1] ,'0')::int,
  civ_text nulls first
civ_text civ_text_start_number
NULL NULL
A NULL
A2 NULL
1/A 34 1
1/A 36 1
2 2
3A 3
4 4
15A 15

(regexp_match(civ_text, '^(\d+)'))[1] to extract start number

coalesce to take care of situation, when there is no leading number

It will be better solution for the future to keep every element of civic number in separate field. Cost of this query may be high.

Solution 2:[2]

You can create two new oders by using regexp_replace.

  1. Find first not number and replace from then until the end and convert to integers.

  2. Remove numbers from beginning and convert to text.

In regular expression postgres:

[^[:digit:]] different from numbers

[[:digit:]] numbers

. any character

* any quantity

+ one or more character

select t.civ_text from (
    (select '10' as civ_text) union               
    (select '12' as civ_text) union
    (select '14' as civ_text) union
    (select '16' as civ_text) union
    (select '2' as civ_text) union
    (select '3' as civ_text) union
    (select '3A' as civ_text) union
    (select '3B' as civ_text) union
    (select '3ABC' as civ_text) union
    (select '4' as civ_text) union
    (select null as civ_text) union
    (select '2/1' as civ_text) union
    (select '2/5' as civ_text) union
    (select '1/B' as civ_text) union
    (select '5' as civ_text)
) as t
order by
    /* Only integers, find first not number and replace from then until the end */
    REGEXP_REPLACE(t.civ_text, '[^[:digit:]].*', '')::integer,
    /* Only characters after numbers at the beginning */
    REGEXP_REPLACE(t.civ_text, '[[:digit:]]+', '')::text

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Kadet
Solution 2

Related Questions