select case statemant MSSQL

[TurnMeOn]

servus...
vielleicht kann mir hier einer helfen...

und zwar habe ich eine tabelle mit strassennamen und hausnummer in einem feld
nun möchte ich in dem feld nach einem leerzeichen+zahl suchen (z.b.: like '% 5%')
und dann ab dem leerzeichen abschneiden, eine neue spalte erstellen (hausnr) und dort dann alles was nach dem leerzeichen kommt hineinschreiben...

vielen dank


also...
habe es jetzt so gemacht...


SELECT
'Strasse' =
CASE
WHEN strasse like '% 1%' or strasse like '% 2%' or strasse like '% 3%' or strasse like '% 4%' or strasse like '% 5%' or strasse like '% 6%' or strasse like '% 7%' or strasse like '% 8%' or strasse like '% 9%'
THEN
rtrim(replace(strasse,case when charindex(' ', strasse)>0 then
case when charindex(' ', strasse, charindex(' ', strasse)+1)>0 then
case when charindex(' ', strasse, charindex(' ', strasse, charindex(' ', strasse)+1)+1)>0 then
case when charindex(' ', strasse, charindex(' ', strasse, charindex(' ', strasse, charindex(' ', strasse)+1)+1)+1)>0 then ''
else replace(substring(strasse, charindex(' ', strasse, charindex(' ', strasse, charindex(' ', strasse)+1)+1), len(strasse)), ' ', '')
end
else replace(substring(strasse, charindex(' ', strasse, charindex(' ', strasse)+1), len(strasse)), ' ', '')
end
else replace(substring(strasse, charindex(' ', strasse), len(strasse)), ' ', '')
end
else ''
end, ''))
ELSE ''
END,

'HausNummer' =
CASE
WHEN strasse like '% 1%' or strasse like '% 2%' or strasse like '% 3%' or strasse like '% 4%' or strasse like '% 5%' or strasse like '% 6%' or strasse like '% 7%' or strasse like '% 8%' or strasse like '% 9%'
THEN
case when charindex(' ', strasse, charindex(' ', strasse)+1)>0 then
case when charindex(' ', strasse, charindex(' ', strasse, charindex(' ', strasse)+1)+1)>0 then
case when charindex(' ', strasse, charindex(' ', strasse, charindex(' ', strasse, charindex(' ', strasse)+1)+1)+1)>0 then ''
else replace(substring(strasse, charindex(' ', strasse, charindex(' ', strasse, charindex(' ', strasse)+1)+1), len(strasse)), ' ', '')
end
else replace(substring(strasse, charindex(' ', strasse, charindex(' ', strasse)+1), len(strasse)), ' ', '')
end
else replace(substring(strasse, charindex(' ', strasse), len(strasse)), ' ', '')
end
else ''
END
FROM kt_adress





Problem bei:
z.B.:

'Stefanmeierstr. 6 a'

dann übernimmt er mir nur das 'a' in die spalte Hausnr und die 6 bleibt beim strassennamen stehen!