T-SQL split string by - and space

I'm having difficult time with T-SQL and I was wondering if somebody could me point me to the right track. I have the following variable called @input

    DECLARE @input nvarchar(100);
    SET @input= '27364 - John Smith';
   -- SET @input= '27364 - John Andrew Smith';

I need to split this string in 3 parts (ID,Firstname and LastName) or 4 if the string contains a MiddleName. For security reason I cannot use functions.

My aproach was use Substring and Charindex.

SET @Id = SUBSTRING(@input, 1, CASE CHARINDEX('-', @input)
                    WHEN 0
                        THEN LEN(@input)
                    ELSE 
                        CHARINDEX('-', @input) - 2
                    END);
        SET @FirstName = SUBSTRING(@input, CASE CHARINDEX(' ', @input)
                    WHEN 0
                        THEN LEN(@input) + 1
                    ELSE 
                        CHARINDEX(' ', @input) + 1
                    END, 1000);
        SET @LastName = SUBSTRING(@input, CASE CHARINDEX(' ', @input)
                    WHEN 0
                        THEN LEN(@input) + 1
                    ELSE 
                        CHARINDEX('0', @input) + 1
                    END, 1000);
Select @PartyCode,@FirstName,@LastName

I am stuck because I don't know how to proceed and also the code has to be smart enough to add a fourth split if Middlename exists.

Any thoughts?

Thanks in advance

Solution

Hopefully this is part of a normalization project. This data is breaking 1NF and one really should avoid that...

Try it like this

The advantages

typesafe values
ad-hoc SQL
set based

If you want you might use a CASE WHEN to check if the last part is NULL and place Part2 into Part3 in this case...

DECLARE @input table(teststring nvarchar(100));
INSERT INTO @input VALUES
(N'27364 - John Smith'),(N'27364 - John Andrew Smith');

WITH Splitted AS
(
    SELECT CAST(N'<x>' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(teststring,N' - ',N' '),N'&',N'&amp;'),N'<',N'&lt;'),N'>',N'&gt;'),N' ',N'</x><x>') + N'</x>' AS XML) testXML
    FROM @input
)
SELECT testXML.value('/x[1]','int') AS Number
      ,testXML.value('/x[2]','nvarchar(max)') AS Part1 
      ,testXML.value('/x[3]','nvarchar(max)') AS Part2 
      ,testXML.value('/x[4]','nvarchar(max)') AS Part3 
FROM Splitted

The result

Number  Part1   Part2   Part3
27364   John    Smith   NULL
27364   John    Andrew  Smith