Thursday 12 November 2015

Comparing full sentences to illegal keywords in a table by TSQL

Comparing full sentences to illegal keywords in a table by TSQL

With the help of this Script we can compare sentences with a table which holds the illegal keywords and based on these keywords values we can mark these sentences as valid or not valid. In this script i created some temporary tables, one of them holds the sentences we need to compare and the other holds the illegal keywords.




/*************************************************************************************************/
--First temp table that holds Sentences needs to compare
--you can replace it with the table which holds the actual sentences
/*************************************************************************************************/

CREATE TABLE #SearchSentance (ID INT,Ttext VARCHAR(4000));
INSERT INTO #SearchSentance
SELECT 1,N'Headset Ball Bearings Chainring Nut This is illegal Sentence'
UNION
SELECT 2,N'This is Correct Sentence'


 SELECT * INTO #SearchTable
 FROM (
SELECT A.ID,
     Split.a.value('.', 'VARCHAR(100)') AS Words
 FROM  (SELECT ID,
         CAST ('<M>' + REPLACE(Ttext, ' ', '</M><M>') + '</M>' AS XML) AS Words
     FROM  #SearchSentance) AS A CROSS APPLY Words.nodes ('M') AS Split(a)
)Q

/*************************************************************************************************/
--This temp table holds the illegal keywords
--you can replace it with the table which holds the actual keywords
/*************************************************************************************************/

CREATE TABLE #Illegal_keyword_Master (ID INT,IllegalKeyWords VARCHAR(4000));
INSERT INTO #Illegal_keyword_Master
SELECT 1,'Bearing' UNION  SELECT 2,'Blade' UNION SELECT 3,' Race' UNION SELECT 4,'Ball' UNION SELECT 5,'Nut'




SELECT
DISTINCT Sentence=stt.Ttext,
[Valid or Not]=CASE WHEN st.id is null THEN 'Legal' ELSE 'Illegal' END
FROM
#SearchSentance stt
LEFT JOIN (
SELECT
*
FROM
#SearchTable st
WHERE
EXISTS(SELECT * FROM #Illegal_keyword_Master im WHERE st.Words=im.IllegalKeyWords)
)st ON st.ID=stt.ID

DROP TABLE #SearchTable
DROP TABLE #SearchSentance
DROP TABLE #Illegal_keyword_Master

No comments:

Post a Comment