Split full name into first name and last name in SQL

SELECT 
    StudentName,
    CASE 
        WHEN CHARINDEX(' ', StudentName) > 0  -- Check if there is a space (indicating a secondary name)
        THEN SUBSTRING(StudentName, 1, CHARINDEX(' ', StudentName) - 1)  -- First name
        ELSE StudentName  -- If no space found, entire name is considered as first name
    END AS Firstname,
    CASE 
        WHEN CHARINDEX(' ', StudentName) > 0
        THEN SUBSTRING(StudentName, CHARINDEX(' ', StudentName) + 1, LEN(StudentName) - CHARINDEX(' ', StudentName))  -- Last name
        ELSE NULL  -- Set last name to NULL if no space found
    END AS Lastname
FROM StudentDetails;

CREATE FUNCTION dbo.SplitStudentName(@StudentName NVARCHAR(100), @Delimiter CHAR(1))
RETURNS TABLE
AS
RETURN
(
    SELECT 
        @StudentName AS StudentName,
        CASE 
            WHEN CHARINDEX(@Delimiter, @StudentName) > 0  -- Check if the delimiter is present
            THEN SUBSTRING(@StudentName, 1, CHARINDEX(@Delimiter, @StudentName) - 1)  -- First name
            ELSE @StudentName  -- If no delimiter found, entire name is considered as first name
        END AS Firstname,
        CASE 
            WHEN CHARINDEX(@Delimiter, @StudentName) > 0
            THEN SUBSTRING(@StudentName, CHARINDEX(@Delimiter, @StudentName) + 1, LEN(@StudentName) - CHARINDEX(@Delimiter, @StudentName))  -- Last name
            ELSE NULL  -- Set last name to NULL if no delimiter found
        END AS Lastname
);