How to make all combinations with given words in SQL

Question

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:

The query must search in the colour, style, marerial and shape field like an or operator
The query must search on each individual search word (like blue, red, modern and wood) with all possible combinations between this words.
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

What are you trying to do here? Is that output what you want, or are you trying to generate something else? — Tim Biegeleisen, May 28 '18 at 13:25
@TimBiegeleisen: Stands in my question. I've updated it so it's clear — H. Pauwelyn, May 28 '18 at 13:29
@H.Pauwelyn Even with the updated explanation I don't understand what you are looking for and what is wrong with your current query. — Marc Guillot, May 28 '18 at 13:43
I guess here is the answer: https://stackoverflow.com/questions/1865353/combining-like-and-in-for-sql-server — niyou, May 28 '18 at 13:50
@TimBiegeleisen: I've added a table with the expected result — H. Pauwelyn, May 28 '18 at 14:07

score 1 · Answer 1 · answered May 28 '18 at 13:57

1

I think there is no clean solution. This statement could do the job:

select distinct colour, style, material, shape
from products 
where colour + style + material + shape like '%blue%' or
      colour + style + material + shape like '%red%' or
      colour + style + material + shape like '%modern%' or
      colour + style + material + shape like '%wood%';

answered May 28 '18 at 13:57

niyou

875
1
11
23

Not really what I need, it's looks like an translation of the `in`-operator. But +1 for the idea – H. Pauwelyn May 28 '18 at 14:25

score 0 · Accepted Answer · answered Jun 01 '18 at 06:13

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);

How to make all combinations with given words in SQL

2 Answers2