View Single Post
  #2 (permalink)  
Old February 6th, 2004, 09:57 PM
ank2go ank2go is offline
Authorized User
 
Join Date: Jun 2003
Location: Rochester, NY, USA.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I guess I'll answer my own post.
Using charindex helps to split up record into
last and first names.

The rest is just using inner join.

Here's the solution w/a little help from monosodiumg:

select *
from
  (select substring(record, 1, charindex(',', record) - 1) as surname,
  substring(record,(charindex(' ', record, 1) + 1), charindex(' ',record,(charindex(' ', record, 1) + 1 )) - (charindex(' ', record, 1) + 1) ) as first_name,
  provid
  from test01
  )t1
inner join
  (select substring(record, 1, charindex(',', record) - 1) as surname,
  substring(record,(charindex(' ', record, 1) + 1), charindex(' ',record,(charindex(' ', record, 1) + 1 )) - (charindex(' ', record, 1) + 1) ) as first_name,
  provid
  from test02
  )t2
      ON t1.provid=t2.provid
      AND t1.surname=t2.surname
      AND t1.first_name=t2.first_name

Reply With Quote