SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

5 rows affected

SELECT TOP 10
     ID
     , FullName
     , SUBSTRING(FullName, 1, CHARINDEX(' ', FullName) - 1) Prefix
     , CASE WHEN CHARINDEX(' ', FullName) = 0 
	           THEN NULL
	        ELSE SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, (CASE WHEN CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) = 0 
			                                                          THEN LEN(FullName)
																   ELSE CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) - 1
			                                                  END)) 
	   END FirstName
     , CASE WHEN CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) + 1 + CHARINDEX(' ', FullName), LEN(FullName))) = 0
	           THEN NULL
			ELSE SUBSTRING(FullName, CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) + 1 + CHARINDEX(' ', FullName), 
			   CASE WHEN CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) = 0
			           THEN LEN(FullName)
			        ELSE ((CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) + 1 + CHARINDEX(' ', FullName), LEN(FullName)))) - 1) END)
	   END MiddleName
	 , CASE WHEN (CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) + 1 + CHARINDEX(' ', FullName), LEN(FullName))) > 0
	           OR CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) > 0)
			   THEN CASE WHEN CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) + 1 + CHARINDEX(' ', FullName), LEN(FullName))) + 1 + CHARINDEX(' ', FullName) + CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))), LEN(FullName))) = 0
			                THEN RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1)
						 ELSE SUBSTRING(FullName, CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) + 1 + CHARINDEX(' ', FullName), LEN(FullName))) + 1 + CHARINDEX(' ', FullName) + CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))), LEN(FullName))
				    END
			ELSE NULL
	   END LastName
	 , LEN(CASE WHEN CHARINDEX(' ', FullName) = 0 
				     THEN NULL
				  ELSE SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, (CASE WHEN CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) = 0 
																		  THEN LEN(FullName)
																	   ELSE CHARINDEX(' ', SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName))) - 1
																  END)) 
		     END) FirstNameChar
FROM dbo.fullNameTest
WHERE
    (FullName LIKE 'Mr %' OR 
     FullName LIKE 'Mrs %' OR  
     FullName LIKE 'Miss %' OR  
     FullName LIKE 'Ms %' OR
     FullName LIKE 'Dr %');

ID	FullName	Prefix	FirstName	MiddleName	LastName	FirstNameChar
1	Mr Lionel Messi	Mr	Lionel	null	Messi	6
2	Mrs Van Dar Sar	Mrs	Van	Dar	Sar	3
3	Miss Lara	Miss	Lara	null	null	4
4	Dr Devi Shetti	Dr	Devi	null	Shetti	4
5	Mr Patrick D-DAR WAK CICILEN	Mr	Patrick	D-DAR	WAK CICILEN	7