Full Name text split in firstname,middlename,lastname and aslo conconate firstname+middlename given below query with database table

 SELECT

    FullName,

    FullName.FirstName,

    CASE

        WHEN 0 = CHARINDEX(' ', FullName.REST_OF_NAME) THEN NULL  --no more spaces? assume rest is the last name

        ELSE SUBSTRING(FullName.REST_OF_NAME, 1, CHARINDEX(' ', FullName.REST_OF_NAME) - 1)

    END AS MiddleName,

    CONCAT(FullName.FirstName, ' ', CASE

                                        WHEN 0 = CHARINDEX(' ', FullName.REST_OF_NAME) THEN NULL

                                        ELSE SUBSTRING(FullName.REST_OF_NAME, 1, CHARINDEX(' ', FullName.REST_OF_NAME) - 1)

                                     END) AS FullNameConcatenated,

SUBSTRING(FullName.REST_OF_NAME, CHARINDEX(' ', FullName.REST_OF_NAME) + 1, LEN(FullName.REST_OF_NAME)) AS LastName

FROM

(

    SELECT

        TITLE.TITLE,

        CASE

            WHEN 0 = CHARINDEX(' ', TITLE.REST_OF_NAME) THEN TITLE.REST_OF_NAME

            ELSE SUBSTRING(TITLE.REST_OF_NAME, 1, CHARINDEX(' ', TITLE.REST_OF_NAME) - 1)

        END AS FirstName,

        CASE

            WHEN 0 = CHARINDEX(' ', TITLE.REST_OF_NAME) THEN NULL

            ELSE SUBSTRING(TITLE.REST_OF_NAME, CHARINDEX(' ', TITLE.REST_OF_NAME) + 1, LEN(TITLE.REST_OF_NAME))

        END AS REST_OF_NAME,

        TITLE.FullName

    FROM

    (

        SELECT

            CASE

                WHEN SUBSTRING(TEST_DATA.Name, 1, 3) IN ('MR ', 'MS ', 'DR ', 'MRS') THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.Name, 1, 3)))

                ELSE NULL

            END AS TITLE,

            CASE

                WHEN SUBSTRING(TEST_DATA.Name, 1, 3) IN ('MR ', 'MS ', 'DR ', 'MRS') THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.Name, 4, LEN(TEST_DATA.Name))))

                ELSE LTRIM(RTRIM(TEST_DATA.Name))

            END AS REST_OF_NAME,

            TEST_DATA.FullName

        FROM

        (

            SELECT REPLACE(REPLACE(LTRIM(RTRIM(FirstNamell)), '  ', ' '), '  ', ' ') AS Name, FirstNamell AS FullName

            FROM

            (

                SELECT FirstNamell FROM tblFamilyMembersDetails_VD

            ) RAW_DATA

        ) TEST_DATA

    ) TITLE

) FullName;

--------------------------------output given below -----------------------------------------