Separate house number from steet name

1

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

2

what if the street is a number 122nd 2345

3

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

4

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

5

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

6

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

7

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
8

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