Hi All,

Thanks in advance for your replies.

I think this is a tricky comparison of string.

Let''s say I have the following tables and fields:

tbl01 tbl02
proid fullname proid fullname
------------- -------------
100 Parker, Peter S. 100 Parker, Peter S. M.
200 Jane, Mary T. 200 Jane, Mary K.
300 Wie, Michelle O. 300 Wie, Michel O.


'fullname' format is lastname, firstname initial.

I would like to return the record on
tbl01.proid=tbl02.proid
and tbl01.(lastname)=tbl02.(lastname)
and tbl01.(firstname)=tbl02.(firstname)

The above example would return 100 and 200.
300 would be excluded b/c 'Michele' <> 'Michel'.

If I had to do this in ASP or C#, then it''s easy.
I could easily split 'fullname' to get first and last name.
But I need to script this in T-SQL.
My box is MS SQL 7.0.

Please help if you know how to do this.
Appreciate all that post !

Thanks You.

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