select * from tb where charindex('abcdefgh',name)>0
select * from tb where patindex('%abcdefgh%',name)>0

select * from tb where name like '%abcdefgh%'

--这样？
--> 测试数据: @os
declare @os table (id int,name varchar(5))
insert into @os
select 1,'abcde' union all
select 2,'de' union all
select 3,'cde' union all
select 4,'abd' union all
select 5,'bcde' union all
select 6,'abe' union all
select 6,'xyz'

declare @name varchar(50)
set @name='abcdefghij'
select * from @os where len(replace(@name,name,''))<len(@name)

declare @str varchar(100)
set @str='abcdefgh'
select * from tblTest where @str like '%'+name+'%'
--> or
select * from tblTest where @str replace(@str,name,'')<>@str

declare @str varchar(100)
set @str='abcdefgh'
select * from tblTest where @str like '%'+name+'%'
--> 笔误：
select * from tblTest where replace(@str,name,'')<>@str

select * from tblTest where 'abcdefgh' like '%'+name+'%'

select * from tblTest where 'abcdefgh' like '%'+name+'%'