String compare in SQL Server !

Question

Hello, I have two table T1 and T2.I used Sql server 2000.

Table T1

ID   WorkerName      Group
1    Rahim,Karim        1
2    Kamal,Adam         3
3    Rahim,Karim        1

Table T2

WorkerID   WorkerName 
1          Rahim
2          Karim
3          Kamal
4          Adam

I want to replace Worker Name on T1 with 1st WorkerID on T2 Like this.

Table T1

   ID   WorkerName      Group
   1      1             1
   2      3             3
   3      1             1

Is there any solution for this?

Answer 1

You need to write a Stored Procedure which runs on table T1 for every record and split the WorkerName using comma separator and execute a query on table T2 to get the id for the workername and update the table T1.

Answer 2

update T1 set WorkerName = T2.WorkerId
from T1, T2
where T1.WorkerName like T2.WokerName + ',%'