Extract substring address SQL

I have a table with column Address which is like city. City_Name, citysector. CitySector, Street. Street_Name, StreetNumber. Street_Number

EXAMPLE
Address
c. Paris, s. ParisDowntown, str. Rue Étienne Marcel, nr. 50

How can I substract 4 different columns like city, sector, street, streetNumber from Address column? Thanks in advance!

Answer

With a couple of replaces you can change your string format to json, and then use openjson to extract the values.

First, create and populate sample table (Please save us this step in your future questions):

DECLARE @T AS TABLE
(
    [Address] nvarchar(200)
);

INSERT INTO @T ([Address]) VALUES 
('c. Paris, s. ParisDowntown, str. Rue Étienne Marcel, nr. 50');

The query:

SELECT City, CitySector, Street, Number
FROM @T
CROSS APPLY OPENJSON('{"' + REPLACE(REPLACE([Address], ', ', '", "'), '. ', '":"') + '"}') 
WITH (
    City nvarchar(100) '$.c',
    CitySector nvarchar(100) '$.s',
    Street nvarchar(100) '$.str',
    Number int '$.nr'
) As vals

Results:

City    CitySector      Street              Number
Paris   ParisDowntown   Rue Étienne Marcel  50