CODEWITHSUNDEEP
sqlpostgresqlsplit
RandomPerson
RandomPerson

Reputation: 790

Postgresql only select letters from a column

So I have this column which contains the street address and the house number, they are both kept in the same column.

Eg: Boulevard 123

Now I want to separate the letters from the numbers so I get "Boulevard" and "123" separately trough a select statement.

How can I do this, using reg expressions?

Thanks in advance

Upvotes: 4

Views: 1705

Answers (2)

Mattew Whitt
Mattew Whitt

Reputation: 2424

The following worked for me.

select 
    substring(address from '\d+') as street_number, 
    substring(address from '[a-zA-Z\s]+') as street_name 
from addresses

Upvotes: 1

Mureinik
Mureinik

Reputation: 311498

One way of doing this is by using regexp_split_to_array to split the value by the space:

SELECT address_arr[1] AS streer_name, address_arr[2] AS street_number
FROM   (SELECT REGEXP_SPLIT_TO_ARRY(address, ' ') AS address_arr
        FROM   my_table) t

Upvotes: 0

Related Questions