Cleaning Names

Hi there, I have the following set of records: (DDL and data provided)


create table dbo.myNames (
FilePersonId int,
Sex varchar(20),
Title varchar(20),
Forename varchar(50),
Surname varchar(50)
)

insert into dbo.myNames (FilePersonId, Sex, Title, Forename, Surname)
select 477827, 'F', 'MISS', 'NATALIE', 'MR NIEL MC DONOUGH' union all
select 516992, 'M', 'MR', 'MARIO.', 'MISS CARLA THRINCHEIRAS' union all
select 1725198, 'M', 'MR', 'THOMAS', 'MS TAGGERT' union all
select 1907673, 'M', 'MR', 'COLIN', 'MR C PATTERSON' union all
select 2282432, 'M', 'MR', 'JOHN', 'MR GOLDSMITH' union all
select 3172847, 'M', 'MR', 'JASON', 'MR JASON MARCUS RENDALL' union all
select 3173628, 'M', 'MR', 'STUART', 'MR STUART IAN JAMES CALLOW' union all
select 3174343, 'M', 'MR', 'JOHANNES', 'MR JOHANNES ARIE JAN DE JAGER' union all
select 3176784, 'F', 'MISS', 'MONICA', 'MISS MONICA S F ALMEIDA' union all
select 3177717, 'M', 'MR', 'MOHAMMAD', 'MR MOHAMMAD NASIR KHAN SABIR' union all
select 3180017, 'M', 'MR', 'MD', 'MR MD. MAMUN UR RASHID' union all
select 3180585, 'F', 'MISS', 'KATHRYN', 'MISS KATHRYN M J GRANT' union all
select 3182306, 'M', 'MR', 'ABDUL', 'MR ABUL K M H KABIR' union all
select 3182416, 'F', 'MRS', 'CATHERINE', 'MRS CATHERINE ANN LEA DOIG' union all
select 3183759, 'M', 'MR', 'PHILIP', 'MR PHILIP ADAM MOXON' union all
select 3183790, 'F', 'MISS', 'KELLY', 'MISS KELLY NEAVE CHAPPELL' union all
select 3191895, 'F', 'MISS', 'LUCY', 'MISS LUCY ANNE MAY GAULT' union all
select 3192189, 'M', 'MR', 'JOAQUIM', 'MR JOAQUIM FILIPE ABRANTES VAZ' union all
select 3192261, 'F', 'MRS', 'C', 'MRS C D V BALBO-BELL' union all
select 3193077, 'M', 'MR', 'NIDAL', 'MR NIDAL FATHEE ABY SABEI' union all
select 3194554, 'M', 'MR', 'STUART', 'MR STUART ROBERT JAMES DOYLE' union all
select 3201437, 'M', 'MR', 'IAN', 'MR IAN' union all
select 3206600, 'M', 'MR', 'ADRIAN', 'MR ADRIAN JOHN JAMES OWEN' union all
select 3206637, 'M', 'MR', 'J', 'MR J O GARCIA CHAURAN' union all
select 3206695, 'M', 'MR', 'SYED', 'MR SYED SAFDAR SHAH' union all
select 3206744, 'M', 'MR', 'LEE', 'MR LEE ALAN JOEL MAYNARD' union all
select 3206763, 'F', 'MISS', 'TANIA', 'MISS TANIA RACHEL NASH' union all
select 3207104, 'M', 'MR', 'ALEXANDER', 'MR ALEXANDER C D HOPWOOD' union all
select 3207577, 'M', 'MR', 'J', 'MR J H S CAMPBELL' union all
select 3207611, 'F', 'MISS', 'TERRI', 'MISS TERRI LOUISE KATE FLEMING' union all
select 3209050, 'F', 'MISS', 'MELISSA', 'MISS MELISSA NEKITA MAKEMSON' union all
select 3213291, 'M', 'MR', 'MALCOLM', 'MR MALCOLM PETER FORD CROOK' union all
select 3215920, 'M', 'MR', 'IAIN', 'MR IAIN ALASTAIR SMITH DOWNEY' union all
select 3889047, 'M', 'MR', 'PEDORA', 'MR LOFARO' union all
select 3974174, 'M', 'MR', 'MICHAEL', 'MR MICHAEL J KERR' union all
select 3977713, 'M', 'MR', 'XAVIER', 'MR XAVIER DE PONTE DE SOUSA' union all
select 3977753, 'M', 'MR', 'JEAN-DANIEL', 'MR JEAN-DANIEL KENNY FLORIAN' union all
select 6815795, 'U', 'UNKNOWN OR INVALID', 'M', 'MR M WOOD' union all
select 7617439, 'F', 'MRS', 'M', 'MRS MARYMARSHALL' union all
select 7929645, 'M', 'MR', 'RICHARD', 'MR PANTER' union all
select 9092854, 'M', 'MR', 'JARED', 'MR JARED ELVANDO BLAKE' union all
select 9109880, 'M', 'MR', 'MICHAEL', 'MR MICHAEL PAUL LLOYD RAMWELL' union all
select 9165326, 'M', 'MR', 'SCOTT', 'MR SCOTT DE LA FUENTE' union all
select 9321272, 'M', 'MR', 'MPHAMMAD', 'MR MPHAMMAD HAMIDI TABAR' union all
select 9461030, 'M', 'MR', 'KELVIN', 'MR KELVIN POH ANN TAN' union all
select 9508257, 'M', 'MR', 'HARDI', 'MR HARDI M IBRAHIM' union all
select 7899594, 'F', 'MRS', 'SANDRA', 'MRSTHPOMPSON' union all
select 9616820, 'U', 'UNKNOWN OR INVALID', 'HAMMOND', 'MRJOHNHAMMOND' union all
select 9617471, 'M', 'MR', 'PAUL', 'MRPAULWAYMAN' union all
select 9817442, 'M', 'MR', 'ADAM', 'MRADAMMARLEY' union all
select 9853852, 'F', 'MISS', 'P', 'MRBARRYGILMARTIN' union all
select 9895683, 'F', 'MRS', 'REBECCA', 'MRSREBECCAWILSON' union all
select 9924241, 'M', 'MR', 'JOHN', 'MRJOHNWORDEN' union all
select 9941612, 'M', 'MR', 'BRIAN', 'MRBRIANOKEEFFE' union all
select 10214780, 'M', 'MR', 'G', 'MRJAMESWADDLE'

What I am trying to do is cleanse the Surname field. I need to remove the Title/Initials and/or Forename from the Title field. But I am struggling to come up with a neat solution to this problem.

Your help would be much appreciated.

Thanks.