SQL query for search user using combine FirstName and LastName column

I want to Searching in User Table should have a FIRSTNAME SECONDNAME option. Eg. "sa pe" should find "Sam Pethers" another Eg. "Cris Jo" should find "Cristina Joya".

Thanks.

Try This if you have the First Name + Last Name combined on a Single Column

DECLARE @Srch VARCHAR(50) ='Cris Jo'
;WITH CTE
AS
(
    SELECT MyNm = 'Sam Pethers'
    UNION ALL
    SELECT MyNm = 'Cristina Joya'
)
SELECT
    *
    FROM CTE
       WHERE 
          LTRIM(RTRIM(SUBSTRING(MyNm,1,CHARINDEX(' ',MyNm)))) LIKE LTRIM(RTRIM(SUBSTRING(@Srch,1,CHARINDEX(' ',@Srch))))+'%'
          AND
          LTRIM(RTRIM(SUBSTRING(MyNm,CHARINDEX(' ',MyNm),LEN(MyNm)))) LIKE LTRIM(RTRIM(SUBSTRING(@Srch,CHARINDEX(' ',@Srch),LEN(@Srch))))+'%'

And This if they are on separate Columns

DECLARE @Srch VARCHAR(50) ='sa pe'
;WITH CTE
AS
(
    SELECT FNm = 'Sam',LNm =  'Pethers'
    UNION ALL
    SELECT FNm = 'Cristina',LNm =  'Joya'
)
SELECT
    *
    FROM CTE
       WHERE 
          FNm LIKE LTRIM(RTRIM(SUBSTRING(@Srch,1,CHARINDEX(' ',@Srch))))+'%'
          AND
          LNm LIKE LTRIM(RTRIM(SUBSTRING(@Srch,CHARINDEX(' ',@Srch),LEN(@Srch))))+'%'