using like tsql with a list of values

Question

In transact sql i have:

 DECLARE @phrase='KeyWord1 KeyWord2 ,KeyWord3 ' -- and my be more separated by space,comma or ;(but mainly by space=it's a phrase)

 I have a table Students
              Students
             (
                StudentId bigint,
                FullName nvarchar(50),
                Article nvarchar(max)
             )

I want to filter students by articles by bringing those whom article conatains a word of @phrase

  Something like:
       DECLARE @WOrdTable TABLE
       (
        Word nvarchar(50)
       )
       INSERT INTO @WOrdTable
       SELECT WOrd of @phrase

   SELECT *
   FROM Students
   WHERE Article LIKE (Word in @phrase)

Slappy · Accepted Answer · 2011-03-30 00:18:42Z

2

I would split your string (comma delimited) into a temp table on your word phrases and perform a join to the Students table. From there you can make better use of the data than you would have in string format

There are plenty ways of splitting a string into a table:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Once you have your temp table you can use something like this.

SELECT S.*
FROM Students S (NOLOCK)
JOIN @tmpArticles A
  ON S.Articles LIKE '%' + A.Article '%'

A word of caution though, using LIKE on %X% has terrible performance, so question your approach if you have a LOT of string data.

This problem seems more geared towards a Full Text Search approach (FTS)

http://msdn.microsoft.com/en-us/library/ms142571.aspx

edited Mar 30, 2011 at 0:18

answered Mar 30, 2011 at 0:07

Slappy

4,1022 gold badges33 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

using like tsql with a list of values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related