searching value from 3 columns

Question

I have one table tbl_FullName. In that table I have three columns named First_name,Middle,Last_name. When user will type his any search text then the result should be returned from all three columns matching the searched text. How can I do that? Please Help me...Thanks in Advance!

Manoj Bhoir · Answer

Hi, You can try this query : DECLARE @SearchText NVARCHAR ( 50 ) = 'Michal Jackson' Select first_name , middle , Last_name From ( Select first_name , middle , Last_name , ( first_name + middle + Last_name ) As FullName from name ) x Where x . FullName like '%' + REPLACE ( @SearchText , ' ' , '%' ) + '%'

Manoj Bhoir · Answer

Welcome Abhijeet.

Abhijeet Deshmukh · Answer

Thank u Manoj...Your query is working. Thank u once again for helping me.

Ramesh Maruthi · Answer

Hi Abhijeet, Agree with Sanjeeb sir answer, but please include one more condition the first and last names also like this : Select First_name+' '+Middle+' '+Last_name as Name from tbl_FullName where First_name = 'Serchtext' or Middle='serchtext' or Last_name= 'serchtext' or (First_name+' '+Middle)='serchtext' or (First_name+' '+Middle+' '+Last_name)='serchtext' or (Middle+' '+Last_name) = 'serchtext' or ( First_name +' '+Last_name) = 'serchtext' // inculde this extra condition too

Sanjeeb Lenka · Answer

try like this Select First_name+' '+Middle+' '+Last_name as Name from tbl_FullName where First_name = 'Serchtext' or Middle='serchtext' or Last_name= 'serchtext' or (First_name+' '+Middle)='serchtext' or (First_name+' '+Middle+' '+Last_name)='serchtext' or (Middle+' '+Last_name) = 'serchtext'

Abhijeet Deshmukh · Answer

It did not work. If I have search text like Michale Jackson then result should be First_Name Middle Last_Name Michael John Jackson Michael Jason Jackson means all occurences of the search query from three columns

Sanjeeb Lenka · Answer

try like this Select Name from(Select First_name as Name from tbl_FullName Union Select Middle as Name from tbl_FullName union Select Last_name as Name from tbl_FullName) where name like '%your searched name%'