searching value from 3 columns

Abhijeet Deshmukh

11y1.1k

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!

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manoj Bhoir11y ago
Accepted 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 Bhoir11y ago
Welcome Abhijeet.
Abhijeet Deshmukh11y ago
Thank u Manoj...Your query is working. Thank u once again for helping me.
Ramesh Maruthi11y ago
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 Lenka11y ago
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 Deshmukh11y ago
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 Lenka11y ago
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%'