Matthias Burger Matthias Burger - 7 months ago 12
SQL Question

Query for a list of string with like

I'm having a table

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




Document


  • 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
foo4711.pdf
,
invoice_bar.pdf
won't be found.
Documents with title
invoce 4711.pdf
or
test4711invoce.png
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)




items

%4711%

%invoice%




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

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