I’m working with two different databases and need to split the combined “Strasse” column into two separate columns: “Strasse” (street) and “Hausnummer” (house number).

Problem:

• Table A: Contains columns strasse (street) and hausnummer (house number).
• Table B: Contains a single column strasse with combined street and house number information (e.g., Examplestreet 5).

Goal:

To split the values from the strasse column in Table B and import them into Table A, separating the street and house number into their respective columns.

Sample Data:

Table A:

Strasse	Hausnummer
Examplestreet	5

Table B:

Strasse
Examplestreet 5

Attempts:

Here’s a query I tried, but it didn’t work as expected:

sqlCopy codeSELECT 
     LEFT(Strasse, LEN(Strasse) - CHARINDEX(' ', REVERSE(Strasse) + ' ')) AS strasse,
     RIGHT(Strasse, CHARINDEX(' ', REVERSE(Strasse) + ' ') - 1) AS hausnummer
FROM TableB

This query didn’t split the data correctly and instead placed the entire street into the Hausnummer column.

Alternate Attempt:

sqlCopy codeSELECT
    LTRIM(RTRIM(REPLACE(Strasse, 
                        CASE 
                            WHEN PATINDEX('%[0-9]%', Strasse) = 0
                                THEN ''
                            ELSE SUBSTRING(Strasse, PATINDEX('%[0-9]%', Strasse), LEN(Strasse) - PATINDEX('%[0-9]%', Strasse) + 1)
                        END, ''))) AS strasse,
    CASE 
        WHEN PATINDEX('%[0-9]%', Strasse) = 0
            THEN ''
        ELSE SUBSTRING(Strasse, PATINDEX('%[0-9]%', Strasse), LEN(Strasse) - PATINDEX('%[0-9]%', Strasse) + 1)
    END AS hausnummer
FROM TableB

This query returned no data in the Hausnummer column.

Question:

How can I correctly split the Strasse column into the strasse (street) and hausnummer (house number) columns in SQL?

Xem thêm bài viết tại https://devtips.online/sql-relationships/