quote:

---

Originally posted by shapper

Hello,

I have an entity named Book with 3 fields: Title, Description and Notes.

And I have two words: "Camões" and "Manuscript".

I would like to get all records that contains one or both words in any of the fields described above.

How can I do this?

---

quote:

---

Originally posted by shapper

Hello,

I have an entity named Book with 3 fields: Title, Description and Notes.

And I have two words: "Camões" and "Manuscript".

I would like to get all records that contains one or both words in any of the fields described above.


And can I do the following?

2 - Order the results by the number of occurrences.

First books where the two words were found.

Second books were only one word was found.
Is this possible?

Thank You,

Miguel

---

CREATE TABLE #temp
(
id INT IDENTITY(1,1),
Title VARCHAR(200),
Description VARCHAR(500),
Notes VARCHAR(150)
)   

-- First books where the two words were found.        
INSERT INTO #temp
SELECT * FROM book
WHERE -- case when both Camões & Manuscript  present in one of the 3 columns in any order
      Title LIKE '%Camões%Manuscript%' OR notes LIKE '%Camões%Manuscript%' OR description LIKE '%Camões%Manuscript%'
      OR Title LIKE '%Manuscript%Camões%' OR notes LIKE '%Manuscript%Camões%' OR description LIKE '%Manuscript%Camões%'
      
--case when Camões Or Manuscript  present in one of the 3 columns and the second search text in some other(2 out of 3) column
      OR Title LIKE '%Camões%' AND (description LIKE '%Manuscript%' OR notes LIKE '%Manuscript%')
      OR description LIKE '%Camões%' AND (Title  LIKE '%Manuscript%' OR notes LIKE '%Manuscript%')
      OR notes LIKE '%Camões%' AND (Title LIKE '%Manuscript%' AND description LIKE '%Manuscript%')
      
      OR Title LIKE '%Manuscript%' AND (description LIKE '%Manuscript%' OR notes LIKE '%Camões%')
      OR description LIKE '%Manuscript%' AND (Title  LIKE '%Camões%' OR notes LIKE '%Camões%')
      OR notes LIKE '%Manuscript%' AND (Title LIKE '%Camões%' AND description LIKE '%Camões%')   


-- Second books were only one word was found.     
INSERT INTO #temp       
SELECT * FROM book
WHERE 
(Title LIKE '%Camões%' AND Title NOT LIKE '%Manuscript%' AND (description NOT LIKE '%Manuscript%' AND notes NOT LIKE '%Manuscript%')) OR
(Title LIKE '%Manuscript%' AND Title NOT LIKE '%Camões%' AND (description NOT LIKE '%Camões%' AND notes NOT LIKE '%Camões%')) OR

(description LIKE '%Camões%' AND description NOT LIKE '%Manuscript%' AND (Title NOT LIKE '%Manuscript%' AND notes NOT LIKE '%Manuscript%')) OR
(description LIKE '%Manuscript%' AND description NOT LIKE '%Camões%' AND (Title NOT LIKE '%Camões%' AND notes NOT LIKE '%Camões%')) OR
      
(notes LIKE '%Camões%' AND notes NOT LIKE '%Manuscript%' AND (Title NOT LIKE '%Manuscript%' AND description NOT LIKE '%Manuscript%')) OR
(notes LIKE '%Manuscript%' AND notes NOT LIKE '%Camões%' AND (Title NOT LIKE '%Camões%' AND description NOT LIKE '%Camões%'))                       
           
SELECT * FROM #temp order by id ASC  -- Order the results by the number of occurrences. 

quote:

---

Originally posted by shapper

In this case I gave two words but can be more.
It was just an example.
Shouldn't I use Full Text Search?

Thank you,
Miguel

---