WITH ProdukteMitTrennern AS
(
    SELECT
        P.Bezeichnung,
        T1.Position1,
        T2.Position2

    FROM dbo.Produkt P

    CROSS APPLY
    (
        SELECT
            CHARINDEX(
                N' - ',
                P.Bezeichnung
            ) AS Position1
    ) T1

    CROSS APPLY
    (
        SELECT
            CHARINDEX(
                N' - ',
                P.Bezeichnung,
                T1.Position1 + 3
            ) AS Position2
    ) T2

    WHERE
        COALESCE(P.Status, N'') <> N'L'
        AND P.Bezeichnung LIKE N'%Sportclub%'
),

RoheHaeuser AS
(
    SELECT
        LTRIM(RTRIM(
            SUBSTRING(
                Bezeichnung,
                Position1 + 3,
                Position2 - Position1 - 3
            )
        )) AS Haus

    FROM ProdukteMitTrennern

    WHERE
        Position1 > 0
        AND Position2 > Position1
),

BereinigteHaeuser AS
(
    SELECT
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        Haus,
                        NCHAR(160),
                        N' '
                    ),
                    NCHAR(8203),
                    N''
                ),
                CHAR(13),
                N''
            ),
            CHAR(10),
            N''
        ) AS Haus

    FROM RoheHaeuser
),

KanonischeHaeuser AS
(
    SELECT
        CASE
            WHEN Haus LIKE N'Sportclub Steinach%'
            THEN N'Sportclub Steinachhof'

            WHEN Haus LIKE N'Sportclub Wal%schlössli'
            THEN N'Sportclub Waldschlössli'

            ELSE Haus
        END AS Haus

    FROM BereinigteHaeuser
)

SELECT DISTINCT
    Haus AS label,
    Haus AS value

FROM KanonischeHaeuser

WHERE
    NULLIF(Haus, N'') IS NOT NULL

ORDER BY
    label;