sql server 2008 - How do I remove unknown characters in a string? -

i delete parts of string.

we have table: locations

mk-mk=new york; sq-al=nej york; en-us=new york  mk-mk=london; sq-al=london; en-us=london  mk-mk=paris; sq-al=paris; en-us=paris 

i want remove , keep sq-al=locationname.

i want result be:

sq-al=nej york; sq-al=london; 

this yet example of importance of normalized databases.
in normalized database have table 2 columns, 1 culture (sq-al, en-us etc`) , 1 value. go step further , have cultures in lookup table.

however, since not case have use string manipulations value of specific culture. can use substring , charindex find specific pattern want.
work in of cases represented sample data i've listed.

-- create table , insert sample data create table location ([name] varchar(100)) insert location ([name]) values  ('en-us=huston; mk-mk=huston; sq-al=huston;'), -- end of row, ending ';'. ('en-us=new york; mk-mk=new york; sq-al=nej york'), -- end of row, without ending ';'. ('mk-mk=london; sq-al=london; en-us=london'),  -- middle of row ('sq-al=paris; en-us=paris; mk-mk=paris') -- begining of row    select  substring(name,          charindex('sq-al=', name), -- index of 'sq-al='         case when charindex(';', name, charindex('sq-al=', name)) > 0 -- if there ';' after 'sq-al='.             charindex(';', name, charindex('sq-al=', name)) -- index of first ';' after 'sq-al='              - charindex('sq-al=', name)  -- index of first ';' - index of 'sq-al=' give length `nej york`         else              len(name)          end     ) + ';' location  -- cleanup drop table location