create function dbo.splitSurname(@n varchar(100)) returns varchar(100) as begin declare @tmp varchar(100) = '', @tmp2 varchar(100) = '' select @tmp = right(@n, len(@n) - charindex (' ', @n)) select @tmp2 = left(@tmp, case when charindex (' ', @tmp) = 0 then len(@tmp) else charindex (' ', @tmp) end) RETURN case when len(@tmp2) > 2 then @tmp2 else '' end end select name,dbo.splitSurname(forename) from dbo.tbl_Name where name like '% %' forename Surname Margarette Aur Aur Arpad Asolt Asolt Mme Aoungranamme Aoungranamme Ewa Aofia Aofia Jennifer Ainnia Ainnia Aheng Aheng Aheng