Compare data in a field with data inside a field

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 2000 Forums

SQL Server Development (2000)

Compare data in a field with data inside a field

Author

Topic

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-10-11 : 10:54:12

Hi All,

I need to compare 2 tables:
table1 : there is a field [Name]
table2 : there are 2 fields Fname & Lname

I need to find the records common to the tables which satisfies the following condition:

table2.Fname --> in the table1.[name] AND table2.Lname --> in the table1.[name]

eg.


Create table #t1 ([Name] varchar(100))
Insert into #t1 values ('aaa, bbb')
Insert into #t1 values ('aaa, ccc')
Insert into #t1 values ('bbb, aaa')
Insert into #t1 values ('aaa  bbb')
Insert into #t1 values ('bbbaaa')
Insert into #t1 values ('aa,a bbb')
Insert into #t1 values ('a aa, bbb')
Insert into #t1 values ('aaaa, bbb')
Insert into #t1 values ('aaacc cbbb')

Create table #t2 ([FName] varchar(50), [LName] varchar(50))
Insert into #t2 values ('aaa','bbb')
Insert into #t2 values ('ac','bbb')

Expected Results of the Query:

FName		LName	Name		
----------------------------------
aaa		bbb	aaa, bbb
aaa		bbb	bbb, aaa
aaa		bbb	aaa  bbb
aaa		bbb	bbbaaa
aaa		bbb	aaaa, bbb
aaa		bbb	aaacc cbbb
ac		bbb	aaacc cbbb

Srinika

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-11 : 11:29:49

[code]
select t2.FName, t2.LName, t1.Name
from #t1 t1 inner join #t2 t2
on t1.Name like '%' + t2.FName + '%'
and t1.Name like '%' + t2.LName + '%'
[/code]

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-10-11 : 11:38:27

Thanks khtan,

Worked great.

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources