Categorization based on keyword in SQL

Question

Dear Team,

I have a table with one column where in I placed all Keywords.

If any Keyword is presented in the subject line of a another column . I will categorize it accordingly. So, How can I do same in SQL ?

Eg :

X Table with One Column (Key Words)
Server Down
Alert Disaster
memory
So On...

In RAWDATA Table, IN X column (Subject Line)

RAWDATA Table with X Column
Server Down ip adress 11.11.331.31
Alert Disaster occurred on yesterday
memory is very high

What I m expecting formula/code has to go through subject line if any key word is identified then in next column that key word needs to be placed.

Expected Result for given example is
Server Down
Alert Disaster
Memory

help is much appreciated !

Please note that currently, I m doing this in Excel but I would like to replicate this in SQL.
Thanks
A

Answer 1

One method is to join the subject column with the keyword table using a LIKE condition.

CREATE TABLE dbo.Keywords(  
	keyword varchar(50) NOT NULL  
		CONSTRAINT PK_Keywords PRIMARY KEY  
);  
INSERT INTO dbo.Keywords  
VALUES  
	 ('Server Down')  
	,('Alert Disaster')  
	,('Memory');  
  
CREATE TABLE dbo.RAWDATA(  
	SubjectLine varchar(100) NOT NULL  
		CONSTRAINT PK_RAWDATA PRIMARY KEY  
);  
INSERT INTO dbo.RAWDATA VALUES  
	 ('Server Down ip adress 11.11.331.31')  
	,('Alert Disaster occurred on yesterday')  
	,('memory is very high');  
  
SELECT rd.SubjectLine, k.Keyword  
FROM dbo.RAWDATA AS rd  
JOIN dbo.Keywords AS k ON rd.SubjectLine LIKE '%' + k.Keyword + '%';  

Answer 2

Hi @Abdul Rahman
You could use the CHARINDEX function to match the keyword and subjectline.
Check this query:

SELECT R.SubjectLine,K.Keyword  
FROM dbo.RAWDATA AS R LEFT JOIN dbo.Keywords AS K ON CHARINDEX(K.Keyword,R.SubjectLine)>0;  

BTW, if you have large data in these two tables, to improve query performance, you'd better make sure that the keyword and subjectline columns have appropriate index.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Thank you for your time and solution.

I will give a try tomorrow in Dev System.

Thanks
A