Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
321 views
in Technique[技术] by (71.8m points)

sql - Find string according to words count

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...