By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.STAGING_1_1_FACT_CUSTOMERS_B2B_LGP
(
Address nvarchar(255),
StreetNumber nvarchar(255),
StreetAddress nvarchar(255)
);
INSERT dbo.STAGING_1_1_FACT_CUSTOMERS_B2B_LGP(Address)
VALUES(N'Gewerbegebiet 5'),
(N'Spännigweg 1'),
(N'Hauptstr 113A'),
(N'Viale Francesco Redi 39'),
(N'Garbage your code does not handle.'),
(N'More garbage 20th promenade 225 W');
; /* <--- ensure previous statement terminated */
WITH src AS
(
SELECT *, FirstNumber =
COALESCE(NULLIF(PATINDEX('%[1-9]%', [Address]), 0),LEN(Address)+1)
FROM dbo.STAGING_1_1_FACT_CUSTOMERS_B2B_LGP
-- WHERE CountryCode IN ('some', 'list');
)
UPDATE src SET
StreetNumber = SUBSTRING(Address, FirstNumber, 255),
StreetAddress = LEFT(Address, FirstNumber-1);
SELECT * FROM dbo.STAGING_1_1_FACT_CUSTOMERS_B2B_LGP;
| Address | StreetNumber | StreetAddress |
|---|---|---|
| Gewerbegebiet 5 | 5 | Gewerbegebiet |
| Spännigweg 1 | 1 | Spännigweg |
| Hauptstr 113A | 113A | Hauptstr |
| Viale Francesco Redi 39 | 39 | Viale Francesco Redi |
| Garbage your code does not handle. | Garbage your code does not handle. | |
| More garbage 20th promenade 225 W | 20th promenade 225 W | More garbage |