sql - Find string according to words count

Question

Is it possible to get below result in SQL Server with single query?!

example data [description_column]:

• LG television
• BOSCH vacuum cleaner 55 mm
• SONY home theater 55 watt

String to search: LG 55 vacuum theater home

UPDATE: I wanna order result according to words count in search term, so desired result is as below:

1. SONY home theater 55 watt (contains three words: 55,theater, home)
2. BOSCH vacuum cleaner 55 mm (contains two words: 55,vacuum)
3. LG television (contains one word: LG)

Thanks in advance

Answer 1

A) Use String_Split or something similar to create a table for each word. LG 55 Vacuum theater home

  #table_of_words (text varchar(32))
  text
  LG
  55
  Vacuum
  Theater
  Home

B) join the created table with the main table using an expression like

SELECT DISTINCT Description 
FROM main_table  Main
JOIN #table_of_words  WD ON Main.Description Like '%'+WD.text+'%'

If you want to only include whole words, you will need to put spaces into the ON clause: (Thanks ZLK)

SELECT DISTINCT Description 
FROM main_table  Main
JOIN #table_of_words  WD ON ' '+Main.Description+' ' 
LIKE '% '+WD.text+' %'

Also note that if you want to deal with commas, you'll need some additional tweaking.

SELECT DISTINCT Description 
FROM main_table  Main
JOIN #table_of_words  WD ON ' '+Main.Description+' ' 
LIKE '%[, ]'+WD.text+'[, ]%'

Here is how to order the result by number of words found

SELECT description,count(*) as NumberWords
FROM main_table  Main
    JOIN #table_of_words WD ON ' '+Main.description+' ' 
    LIKE '% '+WD.text+' %'
GROUP  BY  description
ORDER BY NumberWords DESC