Postgres - split number and letter

Question

I have a Postgres database with addresses. In there are the streetnames in a Column, also in a seperate column are the housenumber with the letters (if they have one).

Example

Streetname               Nr
Haubtstrasse             1
Haubtstrasse             3
Haubtstrasse             3a
Haubtstrasse             3b
Haubtstrasse             5A
Haubtstrasse             5B

I need to spilt those 3a and 5A and 5B into:

Streetname               Nr   Addition
Haubtstrasse             3    a
Haubtstrasse             3    b
Haubtstrasse             5    A
Haubtstrasse             5    B

Can somebody help me with this problem?? like this: enter image description here

Bjorn

Answer 1

You can use substring() with the pattern argument. For your supplied examples:

select substring(nr from '^[0-9]+') as num,
       substring(nr from '[A-Za-z]+$') as addition
from . . .