建议你提供详细的资料：
例如表的结构，表之间的关系，测试数据，相关算法及需要的结果。
这样有助于我们理解你的意思，更主要的是能尽快让你获得答案或解决问题的方法。

------解决方案--------------------

SQL code

create table tb1(id int NOT NULL,big varchar(20),small varchar(20))
insert into tb1(id,big,small)values('1','a','a b c')
insert into tb1(id,big,small)values('2','h','h i j')
insert into tb1(id,big,small)values('3','o','o p q')
go
create table tb2(id int NOT NULL,smallname varchar(20))
insert into tb2(id,smallname)values('1','a c')
insert into tb2(id,smallname)values('2','a h')
insert into tb2(id,smallname)values('3','o p q i')
go

;with ach as
(
    select a.id,a.big,substring(a.small,b.number,charindex(' ',a.small+' ',b.number)-b.number) small
    from tb1 a,master..spt_values b
    where b.type = 'p' and b.number between 1 and len(a.small)
        and len(a.small) >= 1 and substring(' '+a.small,b.number,1) = ' '
)

select distinct b.*
from ach a join tb2 b on charindex(' '+a.small+' ',' '+b.smallname+' ') > 0
where a.big = 'a'

drop table tb1,tb2

/**********************

id          smallname
----------- --------------------
1           a c
2           a h

(2 行受影响)

------解决方案--------------------

SQL code

declare @t1 table(id int,big varchar,small varchar(10))
declare @t2 table(id int,smallname varchar(10))
insert into @t1 select 1,'a','a b c'  
union all select 2,'h','h i j'
union all select 3,'o','o p q'
insert into @t2 select 1,'a c'
union all select 2,'a h'
union all select 3,'o p q i'
declare @input varchar
set @input='a' --例如a
select t2.* from @t2 t2 where charindex(' '+@input+' ',' '+smallname+' ')>0