0

I'm developing an ASP.NET webforms application using C#, and I have two tables in SQL Server, terms and descriptions.

CREATE TABLE Terms
(
    [TermId] [int],
    [TermLable] [varchar](50)
);

CREATE TABLE Descriptions
(
    [DescriptionId] [int],
    [Descriptions] [varchar](MAX)
);

First table includes terms (more than 10K) such as

1     JAVA
2     C++
3     ASP.NET
4     Linux
5     web development
6     programming language

The second table include descriptions such as :

1     Java is the one of the most popular programming languages in the world today. It works on any platform (Windows, Mac, Linux, etc), and is the core language used in developing Android apps. It's a great first language for any aspiring programmer, so whether you want to program Android apps, web apps, or simply learn the foundational skills that all programmers use, this course is a great place to start!
2     This course teaches C++ to students already familiar with a programming language. Students will learn how to use header files, control flow, functions, classes.

I want to create query that searches in each description and finds the matching terms from the first table. so the result would be something like:

The matching terms in first sentence:

JAVA
Linux

The matching terms in second sentence:

C++
programming language

I really tried to think of a way to do this using select %like% but that only works for me with specific terms .. I could not figure out a way of including all these terms from a column.

What is the best possible way of doing it?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Majed Almotairi
  • 33
  • 3
  • What was your attempt to use `%LIKE%` and which terms did it not work with? What happened when it didn't work? – Tab Alleman Aug 21 '18 at 19:52
  • Are your descriptions ever associated with more than one Term? – Ryan Wilson Aug 21 '18 at 19:52
  • What specific terms didn't match against the descriptions you were expecting? Rather than giving us examples which do work, can you give us examples which didn't, And, don't forget, include the SQL you used. – Thom A Aug 21 '18 at 19:54
  • I would suggest exploring the Stuff operation. https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server – Ross Degrand Aug 21 '18 at 19:56

1 Answers1

1

You could use JOIN:

SELECT *
FROM Descriptions d
JOIN Terms t
  ON d.descriptions LIKE '%' + t.TermLable + '%'
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275