CODEWITHSUNDEEP
sqlsql-server
Excited_to_learn
Excited_to_learn

Reputation: 359

Search for a word in the column string and list those words

Have two tables, table 1 with columns W_ID and word. Table 2 with column N_ID and note. Have to list all the NID where words found in table 1 word column contains in Note column (easy part) and also list those words in another column without duplicating the N_ID. Which means using STUFF to concatenate all the words found in Note column for that particular N_ID. I tried using

FULL TEXT INDEX using CONTAIN

But it only allows to search for one word at a time. Any suggestions how I can use a while loop to achieve this. enter image description here

Upvotes: 1

Views: 982

Answers (2)

Steven Ensslen
Steven Ensslen

Reputation: 1376

You can join your tables together based on a postive result from the charindex function.

In SQL 2017 you can run:

SELECT n_id, string_agg(word)
FROM words
inner join notes on 0 < charindex(words.word, notes.note);

Prior to SQL 2017, there is no string_agg so you'll need to use stuff, which is trickier:

select
stuff((
  SELECT ', ' + word
  FROM words
  where 0 < charindex(words.word, notes.note)
  FOR XML PATH('')
  ), 1, 2, '')

from notes;

I used the following schema:

CREATE table WORDS
(W_ID int identity primary key
,word varchar(100)
);

CREATE table notes
(N_ID int identity primary key
,note varchar(1000)
);

insert into words (word) values
('No'),('Nope'),('Nah');

insert into notes (note) values
('I am not going to do this. Nah!!!')
,('It is OK.');

Upvotes: 1

Andrew O&#39;Brien
Andrew O&#39;Brien

Reputation: 1813

If there is a maximum number of words you want displayed for N_ID, you can pivot this. You could have them in a single column by concatenating them, but I would recommend against that. Here is a pivot that supports up to 4 words per N_ID. You can adjust it as needed. You can view the SQL Fiddle for this here.

SELECT
  n_id,
  [1] AS word_1,
  [2] AS word_2,
  [3] AS word_3,
  [4] AS word_4
FROM (
  SELECT
    n_id,
    word,
    ROW_NUMBER() OVER (PARTITION BY n_id ORDER BY word) AS rn
  FROM tbl2
  JOIN tbl1 ON
    tbl2.note LIKE '%'+tbl1.word+'[ ,.?!]%'
  ) AS source_table
  PIVOT (
    MAX(word)
    FOR rn IN ([1],[2],[3],[4])
  ) AS pivot_table

*updated the join to prevent look for a space or punctuation to declare the end of a word.

Upvotes: 1

Related Questions