ElasticSearch - How to match all subwords when querying

Finally, I found an answer for it and hope this could help someone else.

Use "phrase and slop". Below is an Java example

QueryBuilder queryBuilder =             QueryBuilders.multiMatchQuery("milk, drink tea", "function")                .slop(100).type(MatchQueryBuilder.Type.PHRASE);

im not sure if this solution exacly fits you but please:It will return all records which contains all mentioned words, separated with space:

here we start with cleaning before creating new function:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[elasticClasues]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))DROP FUNCTION [dbo].[elasticClasues]go

The function

For @data and each word in @tags function will create clause: @data like '%tag%'

example:

@data = 'desc'

@tags = 'big white expensive'

return: desc like '%big%' and desc like '%white%' and desc like '%expensive%'

create function [dbo].[elasticClasues] (@what varchar(30), @tags nvarchar(1000) ) returns nvarchar(1000) asbegin    declare @i int, @out varchar(1000)    set @i = 0    set @out = ''    if @tags='' return '1=1'    while (LEN(@tags) > 0)    begin        select @i = COALESCE( PATINDEX('% %',@tags ), 0)        if @i = 0        begin            select @out = @out + ' ' + @what + ' like ''%' + @tags + '%'''            return @out        end        else        begin            select @out = @out + ' ' + @what + ' like ''%' + substring(@tags, 1, @i-1) + '%'' and '            select @tags = substring(@tags, @i+1, LEN(@tags))        end    end -- while    return @outendgo

And how to use it:

CREATE TABLE #USER_TABLE(recordid int NOT NULL IDENTITY, data varchar(255) )insert into #USER_TABLE (data) values ('milkway tea'),('tea in shop'),('shop way milk'),('big tea')select * from #USER_TABLE

the USER_TABLE:

recordid    data-------------------------1           milkway tea2           tea in shop3           shop way milk4           big tea

Lets look after records:

A

declare @command nvarchar(1000)set @command = 'select * from #USER_TABLE where ' + dbo.elasticClasues('data','milk')execute SP_EXECUTESQL @command;recordid    data-------------------------1           milkway tea3           shop way milk

B

declare @command nvarchar(1000)set @command = 'select * from #USER_TABLE where ' + dbo.elasticClasues('data','tea')execute SP_EXECUTESQL @command;recordid    data-------------------------1           milkway tea2           tea in shop4           big tea

C

declare @command nvarchar(1000)set @command = 'select * from #USER_TABLE where ' + dbo.elasticClasues('data','tea milk')execute SP_EXECUTESQL @command;recordid    data-------------------------1           milkway tea