Postgres order civic numbers by digits and letters-CodePudding

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

CodePudding user response：

You can create two new oders by using regexp_replace. In the first, remove letters and convert to integers and in the second, remove de numbers e convert to text.

In regular expression postgres:

[[:alpha:]] letters

[[:digit:]] numbers

g all ocurrences

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 '5' as civ_text)
) as t
order by
    /* only integers first */
    REGEXP_REPLACE(t.civ_text, '[[:alpha:]]', '', 'g')::integer,
    /* only letters after */
    REGEXP_REPLACE(t.civ_text, '[[:digit:]]', '', 'g')::text

CodePudding user response：

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.