How to Split Street and House Number into Separate Columns in SQL

I'm working on an SQL problem involving two tables from two different databases. I have Table A with columns `strasse` and `hausnummer` (from Database O), and Table B with the column `strasse` (from Database B). I need to import data from the `strasse` column in Table B into Table A, splitting it into `strasse` and `hausnummer`

Example:

Strasse            Hausnummer
Examplestr.            5

However, in Table B, everything is in one column called "Strasse", like this:

Strasse
Examplestreet 5

I need to separate these and I just can't manage to do it.

Here's what I've tried:

I found some similar queries, none of the solutions provided the desired outcome.

SELECT 
     LEFT(Strasse, LEN(Strasse) - CHARINDEX(' ', REVERSE(Strasse) + ' ')) AS strasse,
    RIGHT(Strasse, CHARINDEX(' ', REVERSE(Strasse) + ' ') - 1) AS hausnummer
FROM TABLE

This did not work. It just puts "Strasse" in "Hausnummer"

And this one:

SELECT
    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
    TABLE

Here the outcome is, that there is nothing in the "Hausnummer" column at all.

Solution

Here's a solution:

-- INIT database
CREATE TABLE MyTable (
  Strasse VARCHAR(100)
);

INSERT INTO MyTable(Strasse) VALUES ('Examplestr.            5');
INSERT INTO MyTable(Strasse) VALUES ('Examplestr. 5');
INSERT INTO MyTable(Strasse) VALUES ('Examplestr.5');
INSERT INTO MyTable(Strasse) VALUES ('Examplestr.');

-- QUERY database
SELECT
  CASE
      WHEN CHARINDEX(' ', Strasse) > 0
      THEN SUBSTRING(Strasse, 1, CHARINDEX(' ', Strasse) - 1)
      ELSE Strasse
  END AS Strasse,
  CASE
      WHEN CHARINDEX(' ', Strasse) > 0
      THEN TRIM(SUBSTRING(Strasse, CHARINDEX(' ', Strasse) + 1, LEN(Strasse)))
  END AS HausNummer
FROM MyTable;

Fiddle.

Explanation:

you get the string part before the space for Strasse
except when there is no space in the string, in which case you get the entirety of Strasse
you get the trimmed right-hand-side value for HausNummer
except when there is no space, in which case it defaults to NULL

EDIT

If there could be multiple spaces, then this should work:

-- INIT database
CREATE TABLE MyTable (
  Strasse VARCHAR(100)
);

INSERT INTO MyTable(Strasse) VALUES ('Examplestr.            5');
INSERT INTO MyTable(Strasse) VALUES ('Examplestr. 5');
INSERT INTO MyTable(Strasse) VALUES ('Examplestr.5');
INSERT INTO MyTable(Strasse) VALUES ('Examplestr.');
INSERT INTO MyTable(Strasse) VALUES ('You could add Neue Schönhauser Straße 16');

-- QUERY database
SELECT
  CASE
      WHEN CHARINDEX(' ', Strasse) > 0
      THEN trim(reverse(substring(reverse(Strasse), CHARINDEX(' ', Strasse), len(Strasse))))
      ELSE Strasse
  END AS Strasse,
  CASE
      WHEN CHARINDEX(' ', Strasse) > 0
      THEN trim(reverse(substring(reverse(Strasse), 0, CHARINDEX(' ', Strasse))))
  END AS HausNummer
FROM MyTable;

Fiddle: https://dbfiddle.uk/ek3175bj

We reverse the string, find the substring to the left and to the right of the first space of the reverted string (which is the last space) and compute based on that the values, defaulting NULL as house number if there is no space and, in the same scenario taking the entire street value as the street value.