Montando un buscador ha surgido la típica funcionalidad de que si introduces una frase, el cliente quiere que se busque por las palabras sueltas no por la frase. Normalmente lo hago desde PHP/Coldfusion (o lo que sea) creando dinámicamente que query pero buscando soluciones alternativas que pueda ejecutar desde un procedimiento almacenado (sin hacer un exec que queda feo) he encontrado esta posible solución:

declare @terminos varchar(max);
set @terminos = 'zapatos bolsos vestidos bufandas';


declare @cadena table (cadena varchar(500)) 
insert into @cadena values (@terminos)
;WITH Vals AS (
	SELECT  cadena,
		CAST('<d>' + REPLACE(cadena, ' ', '</d><d>') + '</d>' AS XML) XmlColumn
	FROM    @cadena
)

SELECT C.value('.','varchar(max)') palabra
FROM    Vals
CROSS APPLY Vals.XmlColumn.nodes('/d') AS T(C)
where len(C.value('.','varchar(max)')) > 1
-- Este último where es para que solo coja las palabras con longitud superior a un carácter

Y esto lo implementaríamos por ejemplo insertando las palabras sueltas en una tabla que usemos luego para hacer un inner join de la siguiente forma:

declare @palabras table (palabra varchar(500)) 

insert into @palabras
SELECT C.value('.','varchar(max)') palabra
FROM    Vals
CROSS APPLY Vals.XmlColumn.nodes('/d') AS T(C)
where len(C.value('.','varchar(max)')) > 1

-- y la consulta resultante sería asi:
select * from productos p 
inner join @palabras tmp on p.descripcion like '%'+tmp.palabra+'%'

 

No sé ni cómo ha surgido la necesidad pero aquí está el uso de la función STUFF:

SELECT STUFF('abcdef', 2, 0, 'HHH');

Para la explicación mejor verlo en la documentación de SQL Server: http://msdn.microsoft.com/es-es/library/ms188043.aspx

Esta es sencillita, para hacer que un string tenga un ancho fijo y relleno las posiciones de la izquierda con ceros. En este caso consiguiendo que la cadena tenga una longitud de 6

select REPLACE(STR(campo, 6), SPACE(1), '0') from table