3

I have a comma delimited string of keywords which I have successfully transformed into a list using a function that takes a @String and returns a TABLE(Value varchar(30))

I can now use this list in a where clause like follows:

SELECT project.*
FROM Projects project 
WHERE project.title IN (SELECT * FROM dbo.ParamsToList('.net,test'))

This matches where project.title is exactly (equal to) any one of the keywords (.net or test).

What I need is to match where title is LIKE '%' + any-keyword + '%'.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Mark Camilleri
  • 130
  • 2
  • 11

2 Answers2

8

One way is like this:

SELECT project.*
FROM Projects project 
WHERE EXISTS
    (
        SELECT * FROM dbo.ParamsToList('.net,test') x 
        WHERE project.title LIKE '%' + x.value + '%'
    )

This approach will mean you don't get the same project returned multiple times, if the project matches multiple values from the params list.

AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
2

Have you tried JOINING the results of the function like this

SELECT DISTINCT project.*
FROM   Projects project
       INNER JOIN dbo.ParamsToList('.net,test') pl ON project.title LIKE '%' + pl.Value + '%'
Lieven Keersmaekers
  • 57,207
  • 13
  • 112
  • 146
  • While this would work, unfortunately i'm in a more complex context where such a join would mess up my results. Thanks! The answer by AdaTheDev below seems to fit my needs – Mark Camilleri May 03 '11 at 09:15
  • 1
    Try to avoid using `JOIN` as a filter predicate. For one, it makes the code more self-explanatory to use `EXISTS`, but also it may multiply the results. In this case when there are records that match both entries. – Gert Arnold Jul 19 '22 at 11:32
  • @GertArnold - funny, I find myself having more trouble reading `EXISTS` than `JOIN`. Probably what I got exposed to first. The `DISTINCT` would take care of the multiple results. Haven't tested it but I do assume the `EXISTS` clause is less overhead. – Lieven Keersmaekers Jul 19 '22 at 15:33
  • It's not entirely my personal preference. There are [good reason for favoring EXISTS in this case](https://stackoverflow.com/q/7082449/861716). Often `DISTINCT` is a patch for duplications that could have been prevented. – Gert Arnold Jul 20 '22 at 13:27
  • @GertArnold - according to that answer the main reason seems to be performance. As the author states: `JOIN syntax is easier to read and clearer normally as well.`. – Lieven Keersmaekers Jul 20 '22 at 13:38