sql server - Fetch on the basis of any sub string -

i have table tbsystemuser feilds firstname , lastname.

i trying fetch on basis @name parameter (which may contain first name or last name).

where  ( tbagent.deletionindicator = 0          , tbsystemuser.systemroletype != 10 )        , ( tbsystemuser.firstname '%' + @name + '%'               or tbsystemuser.lastname '%' + @name + '%' )  

now want should fetch on basis of full name also. e.g first name "lisa m" , last name "iannoti-montz". @name contains lisa m iannoti-montz or @name contains lisa m iannoti or these sub strings. please me!!!

you can try this, word of caution if space between first name , last name gets mismatched not return result wanted. need make sure space between first name , last name.

 (tbsystemuser.firstname '%' + @name + '%'           or tbsystemuser.lastname '%' + @name + '%'            or (tbsystemuser.firstname + ' '+ tbsystemuser.lastname) '%'+ @name + '%') 

or

if dont want care space between first name , last name can use replace remove spaces , match parameter value like,

     (tbsystemuser.firstname '%' + @name + '%'           or tbsystemuser.lastname '%' + @name + '%'            or replace((tbsystemuser.firstname+tbsystemuser.lastname,' ',''), '%'+ replace(@name,' ','') + '%')