SQL SELECT WHERE column has all delimited strings

Question

I have a table with columns like this:

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

I want to have a stored procedure which its input is a string, for example: abc qwe ax xyza The length and how many spaces are free for the user to input.

And what it does is to select all rows which the column bariu contains all of these: abc, qwe, ax, and xyza

Here is what I tried:

I create 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 it, I managed to write something to select all rows which the column bariu contains at least one of these: 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 figure out how to do all instead of at least one of. My question is how to do it?

I am using MS SQL SERVER

Answer 1

You only add 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)