SQL Server : make a select and test every occurrence/row inside another select

Question

I have a SQL Server stored procedure that receives a comma separated string as parameter.

I also have a table-valued function that takes this parameter, splits it (between the commas) and returns as a 'table'.

This procedures is a 'search procedure' that uses LIKE operator to find matching terms.

How can I loop through this parameter that has been transformed into a table and compare it with LIKE?

The sequence that I'd need is something like this:

SQL Server procedure has been called and a separated comma string has been passed as parameter.
A table-valued function gets called to strip this string and transform it in a result table. (It´s not a real table, its just the results). Until here I have already done, the next part is the one I need help:
Loop through this recently created 'table' and search in a specific column of another table.

eg.

SELECT * 
FROM tbl_names 
WHERE col_names LIKE '%' + (the search term here) + '%'

You don't need a loop, just INNER JOIN the tables together using your WHERE clause above as the join condition. — Dave.Gugg, May 13 '15 at 18:31
possible duplicate of [Passing a varchar full of comma delimited values to a SQL Server IN function](http://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function) — Tab Alleman, May 13 '15 at 18:43

Giorgi Nakeuri · Answer 1 · 2015-05-14T06:00:36.593

1

You can join your table on result of your function:

select * from SomeTable st
join dbo.SomeFunction(@str) sf on st.SomeColumn like '%' + sf.Term +'%'

To order by occurences do something like this:

select * from SomeTable st
join(
    select st.ID, count(*) as Occurence from SomeTable st
    join dbo.SomeFunction(@str) sf on st.SomeColumn like '%' + sf.Term +'%'
    group by st.ID) ot on st.ID = ot.ID
order by ot.Occurence desc

edited May 14 '15 at 06:00

answered May 13 '15 at 18:32

Giorgi Nakeuri

35,155
8
47
75

That´s pretty much it, I´m just trying to figure out how to order by biggest number of occurrences, so in case two terms are in the same row, they should be first/before the ocurrences that has only one term matched. – Art May 13 '15 at 19:03

score 0 · Answer 2 · answered May 13 '15 at 19:51

I'd probably use a cross or outer apply with patindex if you want to know how many items matched

select S.*, m.matches 
from sometable s
cross apply (select count(1) as matches from finction where patindex ('%' + function.Column + '%', s.coltosearch) > 1) as matched

Use cross apply if you only want to return rows that have matches and outer if you want all rows with a count of terms.

Note: Code example is untested

SQL Server : make a select and test every occurrence/row inside another select

2 Answers2