T-SQL Like - 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 2000 Forums

Transact-SQL (2000)

T-SQL Like

Author

Topic

cblatchford
Starting Member

40 Posts

Posted - 2006-07-31 : 07:35:49

Hi chaps,

I've been trying to match two tables up based on surnames and forenames using the like command. The reason for this is in table 1 you could have a forename and surname..

Forenames Surname
Joe Bloggs

..then in table 2..

Forenames Surname
Joe Albert Bloggs

I can match up the surname no problem as this only ever contains one name, but the forename can contain multiple names. I've been trying to write a query along the lines of

select * from table1
inner join table2 on table1.surname = table2.surname
and table1.forename like '%[table2.forenames]%'

but its not returning the correct results. I would expect the above to return both Joe Bloggs and Joe Albert Bloggs from each table..

Any ideas!?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-31 : 07:40:45

try this

select * from table1
inner join table2 on table1.surname = table2.surname
and table1.forename like '%' + [table2.forenames] + '%'

cblatchford
Starting Member

40 Posts

Posted - 2006-07-31 : 07:48:26

doh! your a star, thanks khtan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources