Matthias Burger Matthias Burger - 1 year ago 46
SQL Question

Query for a list of string with like

I'm having a table

with some columns e.g.
. Looks like this:


  • Id (uniqueidentifier)

  • DocumentTitle (varchar(200))

in my application you enter a string you search for like "invoice 4711". Now my application shall search for all documents where both of these words are in the title.
So documents with title
won't be found.
Documents with title
invoce 4711.pdf
shall be found.

Since i write this in a procedure i can't set a fix parameter-count for searchstrings. I'm getting one parameter e.g.
invoice 4711
and my split-method returns a table with all items like (and yes, with the %-signs)




Sooo when I try now to get all Documents where both of those values match I get a wrong result (but, yes it makes always sense :D )

info: the fn_split- function takes the searchstring as first parameter and the second parameter is the sliced sign

select *
from document
WHERE 1 = (
select case
when document.documenttitle like items then 1 else 0 end
from fn_split('invoice 4711', ' ')
where document.documenttitle like items

select *
from document
inner join fn_split('invoice 4711', ' ')
on document.DocumentTitle like items

both of these queries returns the result where any of those words are contained - not both.
Any idea what the issue is here? or how the query works right?

Answer Source

Count them.

declare @arg varchar(100) ='invoice 4711';
select * 
from document
cross apply ( 
select n=count(*) from fn_split(@arg, ' ') x where
 document.DocumentTitle like x.items) cnt
where (select count(*) from fn_split(@arg, ' ')) = cnt.n;