CODEWITHSUNDEEP
sqlpostgresqlsplit
Bjorn ten Broeke
Bjorn ten Broeke

Reputation: 145

Postgres - split number and letter

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

Upvotes: 4

Views: 4453

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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 . . .

Upvotes: 4

Related Questions