I have a stored procedure that is used for customer search. Curently i am having issues geeting searh results when the MiddleName is ommitted.

Below is a breakdown of the error
if a customer name have middlename, e.g. James Mark Luke
and i search with James Luke, the record does not appear

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetUsers`(IN p_search varchar(50), IN p_careofficer int,
                                                       IN p_gender int, IN p_status int, IN p_holdertier int,
                                                       IN p_signuptype int, IN p_datefrom date, IN p_dateto date,
                                                       IN p_pagenumber int, IN p_pagesize int, IN p_sortkey varchar(10),
                                                       IN p_sortorder int)
BEGIN
DECLARE page_offset INT;
        SET p_search = TRIM(p_search); 
        SET p_pagenumber = IFNULL(p_pagenumber, 1) -1;
        SET p_pagesize = IFNULL(p_pagesize, 50);
        SET page_offset = p_pagenumber * p_pagesize;

SELECT 
    c.Id,
    c.UserId,
    c.FirstName,
    c.MiddleName,
    c.LastName,
    c.Gender,
    c.DateOfBirth,
    c.EmailAddress,
    c.MobileNumber,
    c.Status,
    c.CreatedOn,
    c.SignUpType,
    c.HolderTier,
    c.WalletNumber
FROM
    User c
WHERE
    c.IsDeleted = 0
        AND (p_search IS NULL
OR CONCAT_WS(' ', c.FirstName, c.MiddleName, c.LastName) LIKE CONCAT('%', TRIM(COALESCE(p_search, '')), '%')
        OR c.MobileNumber LIKE CONCAT('%', TRIM(COALESCE(p_search, '')), '%')
        OR c.BVN LIKE CONCAT('%', TRIM(COALESCE(p_search, '')), '%')
        OR c.EmailAddress LIKE CONCAT('%', TRIM(COALESCE(p_search, '')), '%'))
        AND (p_careofficer IS NULL
        OR c.CareOfficer = p_careofficer)
        AND (p_gender IS NULL OR c.Gender = p_gender)
        AND (p_status IS NULL OR c.Status = p_status)
        AND (p_holdertier IS NULL
        OR c.HolderTier = p_holdertier)
        AND (p_signuptype IS NULL
        OR c.SignUpType = p_signuptype)
        AND (p_datefrom IS NULL
        OR c.CreatedOn BETWEEN p_datefrom AND p_dateto)
ORDER BY c.CreatedOn DESC
LIMIT p_pagesize OFFSET page_offset;
END

I have tried using

1. IFNULL(MiddleName, ”)
2. COALESCE(MiddleName)

Both have not yielded the much expected result.

Previosusly, i was using FullText Search(FTS) but that was completeley overhauled.