/*
-- Author:Flystone 
-- Date:2008-05-15 
-- Version:V1.001
*/

-- Test Data: Ta
If object_id('Ta') is not null 
    Drop table Ta
Go
Create table Ta(ID int,Name varchar(2))
Go
Insert into Ta
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc' 
-- Test Data: Tb
If object_id('Tb') is not null 
    Drop table Tb
Go
Create table Tb(ID varchar(10),age varchar(2))
Go
Insert into Tb
select '1,2,3','FF'
Go
--Start
select a.*,b.age
from ta a left join tb b
on charindex(','+ltrim(a.id)+',',','+b.id+',') > 0


--Result:
/*

ID          Name age  
----------- ---- ---- 
1           aa   FF
2           bb   FF
3           cc   FF

（所影响的行数为 3 行）


*/
--End 

CREATE TABLE t1  
( 
id varchar(10), 
age varchar(2) 
) 
insert into t1 values('1,2,3','FF') 
create table t2 
( 
id varchar(5), 
name char(2) 
) 
insert into t2 
select  
1, 'aa' union 
select  
2, 'bb' union 
select  
3, 'cc' 


select b.id,b.name,a.age from t1 a,t2 b
where charindex(b.id,a.id)>0

CREATE TABLE t1  
( 
id varchar(10), 
age varchar(2) 
) 
insert into t1 values('1,2,3','FF')
insert into t1 values('4','GG')
    
create table t2 
( 
id varchar(5), 
name char(2) 
) 
insert into t2 
select  
1, 'aa' union 
select  
2, 'bb' union 
select  
3, 'cc' union 
select  
4, 'dd' 



select b.id,b.name,a.age from t1 a,t2 b
where charindex(','+b.id+',',','+a.id+',')>0

/*
id    name age
----- ---- ----
1     aa   FF
2     bb   FF
3     cc   FF
4     dd   GG

(4 行受影响)
*/

select b.*,a.age
from tab1 as a
right outer join
tab2 as b
on charindex(','+ltrim(rtrim(b.id))+',',','+a.id+',') > 0

update t set name = b.age from b t,a x where charindex(','+rtrim(t.id)+',',','+x.id+',')>0

CREATE TABLE A
( 
id varchar(10), 
age varchar(2) 
) 
insert into A values('1,2,3','FF') 

create table B( 
id varchar(5), 
name char(2) 
) 
insert into B
select  
1, 'aa' union 
select  
2, 'bb' union 
select  
3, 'cc' 

select B.id,B.name,A.age from A,B where charindex(B.id,A.id)>0