SQL SELECT WHERE column contains all delimited rows

Question

I have a table with such columns:

Table: teeburu Columns: - aidi int identity - bariu varchar(8000)

I want to have a stored procedure for which its input is a string, for example: abc qwe ax xyza Length and number of free places for the user.

And all he does is select all the rows, the bariu column includes all of them: abc , qwe , ax and xyza

Here is what I tried:

I am creating a split function (copied from this page )

 create FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1)) returns @temptable TABLE (nameIndex int identity(1,1),items varchar(8000)) as begin declare @idx int declare @slice varchar(8000) select @idx = 1 if len(@String)<1 or @String is null return while @idx!= 0 begin set @idx = charindex(@Delimiter,@String) if @idx!=0 set @slice = left(@String,@idx - 1) else set @slice = @String if(len(@slice)>0) insert into @temptable(Items) values(@slice) set @String = right(@String,len(@String) - @idx) if len(@String) = 0 break end return end 

With him, I managed to write something to select all the rows in the bariu column which contain at least one of them: abc , qwe , ax and xyza

 with list as( select * from dbo.split(@Param,' ') ) SELECT aidi, bariu FROM teeburu INNER JOIN list ON bariu like '%'+ list.items+ '%' GROUP BY aidi, bariu 

But I can’t understand how to do everything instead of at least one of them . My question is: how to do this?

I am using MS SQL SERVER

Answer 1

You add only HAVING COUNT(*) = (SELECT COUNT(*) FROM list)

 with list as( select * from dbo.split('abc qwe ax xyza',' ') ) SELECT aidi, bariu FROM teeburu INNER JOIN list ON bariu like '%'+ list.items+ '%' GROUP BY aidi, bariu HAVING COUNT(*) = (SELECT COUNT(*) FROM list) 

Answer 2

I will also modify the split function to get the number of parameters as well.

The execution of this will not increase, it will not increase my task and there will be no imformact perfromance.

 TABLE (nameIndex int identity(1,1),items varchar(8000),ParamCount int) 

Then you already loop so that you take one variable and increase its value on each cycle, insert the value of the variable here,

 insert into @temptable(Items,ParamCount) values(@slice,@Newvar) 

Advantage: you avoid the single statement count (*).

 SELECT COUNT(*) FROM list 

So the final request,

  SELECT aidi ,bariu FROM teeburu CROSS APPLY ( SELECT Items ,paramcount FROM dbo.split('abc qwe ax xyza', ' ') ) list WHERE bariu LIKE '%' + list.items + '%' GROUP BY aidi ,bariu HAVING COUNT(*) = list.paramcount 

It also depends on your actual query and table. Maybe you should use row_number instead of a group with so many columns