Combining 2 Columns in Search - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Combining 2 Columns in Search

Author

Topic

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-10-10 : 17:53:17

I am trying to Search for a String where it involes 2 columns and i am unable to figure out how to use that in Where condition. can anybody pls.help

columns to combine
1) firstname
2) lastname

Select * from table
where firstname + lastname like '%John Master%'

other than using views, FULLTEXT cataglogs, is there a way to do this.

the above query doesn't work..

thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-10 : 18:08:25

I've tested,
your above query basically works but maybe there are some controlchars in it like newline carriage return or tab?

Webfred

Planning replaces chance by mistake

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-10-10 : 18:11:50

works if the Search String "John Master" exists in One Column, if "John" exists in Column1 and "Master" exists in Column2 query fails.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-10 : 18:15:32

that works fine:


create table #test(
first varchar(255),
last varchar(255)
)
insert #test
select 'web','fred' union
select 'web','test'

select * from #test 
where first+last like '%webt%'

drop table #test

Planning replaces chance by mistake

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-10 : 18:21:42

firstname + lastname returns 'JohnMaster'
ltrim(rtrim(firstname)) +' '+ltrim(rtrim(lastname)) returns 'John Master'

Select * from table
where ltrim(rtrim(firstname)) +' '+ltrim(rtrim(lastname)) like '%John Master%'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-10 : 18:25:22

that's right!

Planning replaces chance by mistake

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-11 : 01:30:24

and remember presence of NULL values in any of the columns can return concatenated result as NULL unless you've CONCATE NULL YIELDS NULL setting off. so better to modify it like below

Select * from table
where ltrim(rtrim(coalesce(firstname,''))) +' '+ltrim(rtrim(coalesce(lastname,''))) like '%John Master%'

sql1_learner
Starting Member

6 Posts

Posted - 2008-10-13 : 04:34:48

select first_name +' ' + last_name from table
where
first_name like '%John%' and last_name like '%Master%'

it wil work..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 04:42:15

quote:
Originally posted by sql1_learner

select first_name +' ' + last_name from table
where
first_name like '%John%' and last_name like '%Master%'

it wil work..

this will include even names with some values in between John and Master like John Mathew Master, John Valentine Master,... while other queries return only those having John Master near each other without anything other than ' ' in between.

renu
Starting Member

47 Posts

Posted - 2008-10-13 : 04:46:20

Select * from table
where firstname + lastname like '%John%+ '%Master%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 05:00:19

quote:
Originally posted by renu

Select * from table
where firstname + lastname like '%John%+ '%Master%'

again the % at end of John ensures it takes all data which has any names between John and Master returned also.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources