like operator in sql server 2008

 I want to search with Example : Pabitra Behera
-----------------------------------------------
Case 1: Search From Start
select * from Mst_Emp where Cat_Name like  'pabitra'+'%'--Result: Pabitra Behera
select * from Mst_Emp where Cat_Name like  'Behera'+'%'---Result: No Result
create Procedure Ret_EmployeeData
as begin
select * from Mst_Emp where Cat_Name like  'pabitra'+'%'
end
-----------------------

Case 1: Search From Any Position
select * from Mst_Emp where Cat_Name like  '%'+'pabitra'+'%'--Result :Pabitra Behera
select * from Mst_Emp where Cat_Name like  'Behera'+'%'--Result :Pabitra Behera
create Procedure Ret_EmployeeData
as begin
select * from Mst_Emp where Cat_Name like  '%'+'pabitra'+'%'--Result :Pabitra Behera
end