How to make all combinations with given words in SQL

I’ll make a search query that search on each individual word contains in my table using SQL Server. The query must work as following situation:

1. The query must search in the colour, style, marerial and shape field like an or operator
2. The query must search on each individual search word (like blue, red, modern and wood) with all possible combinations between this words.
3. The search values must contain in a column like an like operator.

This means for this words that I only can find the products with [[colour like '%blue%' or colour like '%red%'], material like '%wood%', style like '%modern%' and each shape]. (required output) in other words all the combinations with each word.

Here is my query so far:

select distinct colour, style, material, shape
from products
where colour in ('blue', 'red', 'modern', 'wood') or
      style in ('blue', 'red', 'modern', 'wood') or
      material in ('blue', 'red', 'modern', 'wood') or
      shape in ('blue', 'red', 'modern', 'wood') ;

This is the result:

colour	style	material	shape
Modern	Aluminum	Round
Modern	Metal	Round
Albast	Modern	Acrylic	Round
Albast	Modern	Glass	Cylinder
Albast	Modern	Glass	Other
Albast	Modern	Glass	Rectangle
Albast	Modern	Glass	Round
Albast	Modern	Glass	Square
Albast	Modern	Synthetic Material	Globe
Albast	Modern	Synthetic Material	Round
Amber	Modern	Steel	Round
Black	Cottage	Wood
Black	Cottage	Wood	Round
Black	Modern	Reflector
Black	Modern	Abs	Round
Black	Modern	Acrylic	Round
Black	Modern	Aluminum
Black	Modern	Aluminum	Corner-Shaped
Black	Modern	Aluminum	Cylinder
Black	Modern	Aluminum	Half-Round
Black	Modern	Aluminum	Other
Black	Modern	Aluminum	Oval
Black	Modern	Aluminum	Rectangle
Black	Modern	Aluminum	Round
Black	Modern	Aluminum	Square
Black	Modern	Cotton	Hexagon
Black	Modern	Cotton	Round
Black	Modern	Glass	Rectangle

But I see that the result is bases on one or many word could be find or not.

I’ve also tried this query but found no results.

select distinct colour, style, material, shape
from products
where colour in ('blue', 'red', 'modern', 'wood') and
      style in ('blue', 'red', 'modern', 'wood') and
      material in ('blue', 'red', 'modern', 'wood') and
      shape in ('blue', 'red', 'modern', 'wood') ;

I’m not able to find if a word is a colour, shape, style or material.

Update: Expected result

colour	style	material	shape
blue	modern style	wood	round
red	modern	wood	Rectangle
red	modern	wood	round
blue	modern	wood	Rectangle
blue	modern	wood	globe
red	modern	wood	globe

Answer

After searching for a while, I’ve found this solution:

• I’ve created a stored procedure whit one parameter: @query.
• Then I split my query on spaces. See the accepted answer from this question: Splitting the string in sql server.
• Then I made my select statement.
• And for every part of my query I add my where clause and replace {0} with my query part.
• Then just add 1 = 1 to got a valid SQL query.
• Execute my generated SQL query using EXEC statement.
• Last execute my stored procedure.

Here is my code:

declare @sql nvarchar(max);
declare @q nvarchar(max);
declare @whereClause nvarchar(max);
declare @currentrow int = 1;

declare @totalqueries int = (select count(pn) 
                             from dbo.SplitString(' ', @query)); -- count how many query 
                                                                 -- parts I've got.

set @sql = 'select distinct colour, style, material, shape
            from products 
            where '; -- my select statement

set @whereClause = 'colour + style + material + shape like ''%{0}%'' and '; -- where clause

while @currentrow <= @totalqueries begin;

    select @q = s 
    from dbo.SplitString(' ', @query)
    where pn = @currentrow;

    set @sql = @sql + REPLACE(@whereClause, '{0}', @q); -- replacing `{0}` with my 
                                                        -- query part `@q`

    set @currentrow = @currentrow + 1;

end;

set @sql = @sql + ' 1 = 1;';

exec (@sql);

See also this function:

create FUNCTION dbo.SplitString (@sep nvarchar(1), @s varchar(4000))
    RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
        SELECT 1, 1, CHARINDEX(@sep, @s)
        UNION ALL
        SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
        FROM Pieces
        WHERE stop > 0
    )
    SELECT pn,
        SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS s
    FROM Pieces
)