# Separate house number from steet name

Hello
I try to separate the house number from the street name. I have in one column the following data:
street
Ruschgrstr. 24
Austr 4a
Zhstr 5a/7b
…...

I need to make a query and to put the street in two separate columns
street house number
Ruschgrstr. 24
Austr 4a
Zhstr 5a/7b
…..

Any ideas?

I have the query, but it still doesn't work:

SELECT
LTRIM(RTRIM(LEFT(Table.Street ,PATINDEX('% [0-9]%',Table.Street )-1))) AS Street,

LTRIM(RTRIM(RIGHT(Table.Street , LEN(Table.Street ) - PATINDEX('% [0-9]%',Table.Street ) +1))) AS Street_number

FROM Table

yes this also possible
every combination for street and number with number and charakter are possible
The number is always behind the text

thanks

what if the street is a number 122nd 2345

SELECT
LTRIM(RTRIM(LEFT(Table.Street +' 0' ,PATINDEX('% [0-9]%',Table.Street )-1))) AS Street,
LTRIM(RTRIM(SUBSTRING(Table.Street +' 0', PATINDEX('% [0-9]%',Table.Street ) +1, 1000))) AS Street_number

yes this also possible
every combination for street and number with number and charakter are possible
The number is always behind the text

Presumably there wouldn't be a leading space in '122nd 2345'. The patindex is searching for space followed by a number.

Thank you
with my solution and wth yours I get the message " Invalid length parameter passed to the LEFT or SUBSTRING function." How could I avoid this?
With your solution I get for the house numbers in addition .0 to every house number
|Au|8 0
|Ruschgraben|810 0
|Bahnhofstrasse|12b 0
|Buba|12/3 0

many thanks

D'oh, sorry, in a hurry and I put the " + ' 0' " in the wrong place / on the wrong value:

SELECT
LTRIM(RTRIM(LEFT(Table.Street,PATINDEX('% [0-9]%',Table.Street +' 0' )-1))) AS Street,
LTRIM(RTRIM(SUBSTRING(Table.Street, PATINDEX('% [0-9]%',Table.Street + ' 0' ) +1, 1000))) AS Street_number

For example:
SELECT
LTRIM(RTRIM(LEFT(table1.Street,PATINDEX('% [0-9]%',table1.Street +' 0' )-1))) AS Street,
LTRIM(RTRIM(SUBSTRING(table1.Street, PATINDEX('% [0-9]%',table1.Street + ' 0' ) +1, 1000))) AS Street_number
FROM (values('Ruschgrstr. 24'),('Austr 4a'),('Zhstr 5a/7b'),('122nd 2345'),('1600 Penn Ave.'))table1(street)

2 Likes

it works great and without any errors
thank you very much !!!!!