Q&A for those interested in the geology, meteorology, oceanography, and environmental sciences
DECLARE @t AS TABLE (search varchar(100)); INSERT INTO @t VALUES ('avi'); DECLARE @words AS TABLE (word varchar(100)); INSERT INTO @words VALUES ('avion'), ('iva'), ('name'); with cte as ( select substring(search, 1, 1) as letter, stuff(search, 1, 1, '') as search, 1 as RowID from @t union all select substring(search, 1, 1) as letter, stuff(search, 1, 1, '') as search, RowID + 1 as RowID from cte where len(search) > 0 ) ,letters AS ( SELECT DISTINCT letter FROM cte ) SELECT words.word FROM letters INNER JOIN @words AS words ON CHARINDEX(letter, word) > 0 GROUP BY words.word HAVING COUNT(*) = (SELECT COUNT(*) FROM letters)