Split full name into first name and last name in SQL

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

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