declare @Word1 varchar(100), @Word2 varchar(100), @Word3 varchar(100)

declare @str varchar(100) = 'Word1,Word2,Word3'
--declare @str varchar(100) = ',Word2,'
--declare @str varchar(100) = ',,Word3'
--declare @str varchar(100) = 'Word1,,'
--declare @str varchar(100) = ',,'

SELECT  @Word1 = LEFT(@str, CHARINDEX(',',@str)-1)
       ,@Word2 = LEFT(SUBSTRING(@str, CHARINDEX(',',@str)+1,LEN(@str)), CHARINDEX(',',SUBSTRING(@str, CHARINDEX(',',@str)+1,LEN(@str)))-1)
       ,@Word3 = REVERSE(LEFT(REVERSE(@str), CHARINDEX(',',REVERSE(@str))-1))

select @Word1,@Word2,@Word3

declare @string varchar(100) = 'surname, forename, middlename'
declare @string varchar(100) = ', forename, middlename'

select *
from dbo.TableFromList(@string)



DECLARE @Master_Surname NVARCHAR(200)
DECLARE @Master_Forename NVARCHAR(200)
DECLARE @Master_MiddleName NVARCHAR(200)
		

select @Master_Surname = substring(@string, 1, charindex(',', @string)-1)
select @Master_Surname
select @Master_Forename = substring(ltrim(replace(@string, @Master_Surname+',', '')), 1, charindex(',', (ltrim(replace(@string, @Master_Surname+',', ''))))-1)
select @Master_Forename
select @Master_MiddleName = reverse(left(reverse(@string), charindex(',', reverse(@string))-1))
select @Master_MiddleName

declare @Word1 varchar(100), @Word2 varchar(100), @Word3 varchar(100)

declare @str varchar(100) = 'Word1,Word2,Word3'
--declare @str varchar(100) = ',Word2,'
--declare @str varchar(100) = ',,Word3'
--declare @str varchar(100) = 'Word1,,'
--declare @str varchar(100) = ',,'

SELECT @Word1 = LEFT(@str, FC-1) 
      ,@Word2 = SUBSTRING(@str, FC+1, SC-FC-1) 
      ,@Word3 = SUBSTRING(@str, SC+1, LEN(@str)) 
FROM   (SELECT  @str S) Q
CROSS APPLY (SELECT CHARINDEX(',',@str) FC) A1
CROSS APPLY (SELECT CHARINDEX(',',@str,A1.FC+1) SC) A2

select @Word1,@Word2,@Word3

DECLARE @Word1 VARCHAR(8000), @Word2 VARCHAR(8000), @Word3 VARCHAR(8000)

SELECT @Word1 = CASE ItemNumber WHEN 1 THEN Item ELSE @Word1 END
    ,@Word2 = CASE ItemNumber WHEN 2 THEN Item ELSE @Word2 END
    ,@Word3 = CASE ItemNumber WHEN 3 THEN Item ELSE @Word3 END
FROM dbo.DelimitedSplit8K('Word1, Word2, Word3', ',')

SELECT @Word1, @Word2, @Word3

	SET @Master_Surname = LEFT(@Master_mkNormalisedName, CHARINDEX(',',@Master_mkNormalisedName)-1)
		SET @Master_Forename = LEFT(SUBSTRING(@Master_mkNormalisedName, CHARINDEX(',',@Master_mkNormalisedName)+1,
														LEN(@Master_mkNormalisedName)), CHARINDEX(',',SUBSTRING(@Master_mkNormalisedName, 
														CHARINDEX(',',@Master_mkNormalisedName)+1,LEN(@Master_mkNormalisedName)))-1)
		SET @Master_MiddleName = REVERSE(LEFT(REVERSE(@Master_mkNormalisedName), CHARINDEX(',',REVERSE(@Master_mkNormalisedName))-1))
		
		-- get the normalised name parts and the phonetic name parts for the duplicate record
		SET @Duplicate_Surname = LEFT(@Duplicate_mkNormalisedName, CHARINDEX(',',@Duplicate_mkNormalisedName)-1)
		SET @Duplicate_Forename = LEFT(SUBSTRING(@Duplicate_mkNormalisedName, CHARINDEX(',',@Duplicate_mkNormalisedName)+1,
														LEN(@Duplicate_mkNormalisedName)), CHARINDEX(',',SUBSTRING(@Duplicate_mkNormalisedName, 
														CHARINDEX(',',@Duplicate_mkNormalisedName)+1,LEN(@Duplicate_mkNormalisedName)))-1)
		SET @Duplicate_MiddleName = REVERSE(LEFT(REVERSE(@Duplicate_mkNormalisedName), CHARINDEX(',',REVERSE(@Duplicate_mkNormalisedName))-1))
		

-- get the normalised name parts and the phonetic name parts for the master record
		SELECT @Master_Surname = CASE ItemNumber WHEN 1 THEN Item ELSE @Master_Surname END
		,@Master_Forename = CASE ItemNumber WHEN 2 THEN Item ELSE @Master_Forename END
		,@Master_MiddleName = CASE ItemNumber WHEN 3 THEN Item ELSE @Master_MiddleName END
		FROM dbo.DelimitedSplit8K(@Master_mkNormalisedName, ',')

		-- get the normalised name parts and the phonetic name parts for the duplicate record
		SELECT @Duplicate_Surname = CASE ItemNumber WHEN 1 THEN Item ELSE @Duplicate_Surname END
		,@Duplicate_Forename = CASE ItemNumber WHEN 2 THEN Item ELSE @Duplicate_Forename END
		,@Duplicate_MiddleName = CASE ItemNumber WHEN 3 THEN Item ELSE @Duplicate_MiddleName END
		FROM dbo.DelimitedSplit8K(@Duplicate_mkNormalisedName, ',')