Soundex y Like

[Andres95]

Código:

create table #temp ([name] varchar(30))

insert into #temp values('Jose')
insert into #temp values('José')
insert into #temp values('Juan José')
insert into #temp values('Juan Jose')

print 'Consultando con colation no sensitiva a acentos (AI)'
SELECT * from #temp where [name] like '%jose%' COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI

print 'Consultando con colation sensitiva a acentos (AS)'
SELECT * from #temp where [name] like '%jose%' COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS

Resultados 

Consultando con colation no sensitiva a acentos (AI)
name                           
------------------------------ 
Jose
José
Juan José
Juan Jose

Consultando con colation sensitiva a acentos (AS)
name                           
------------------------------ 
Jose
Juan Jose