Term Matching using SQL

Question

I would like to split the text in the database and see whether the all the term that I searched is in the text.

For example, "this is a cat" is the text in the database. If I search for "a cat" or "is cat" it should return the data but it shouldn't return anything if i searched for "a ca" or "cat a".

I can do this in the code but I would like to know if it is possible to do it in the query.

Ajay Gupta · Answer 1 · 2018-06-26T05:41:57.273

Something like this, perhaps?

DECLARE @text NVARCHAR(400) = 'this is a cat'  
DECLARE @search NVARCHAR(400) = 'a cat'  

SELECT t.value  
FROM STRING_SPLIT(@text, ' ') t
join STRING_SPLIT(@search, ' ') s
on t.value = s.value
WHERE RTRIM(t.value) <> '' and RTRIM(s.value) <> '';

Then you can compare the if the count of this result and your search split count is equal.

You'll need Sql-server 2016 or higher for STRING_SPLIT to work.

In case of lower versions of sql, you'll need to create a UDF to split a string. One like this:

CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO

And the above query becomes:

DECLARE @text NVARCHAR(400) = 'this is a cat'  
DECLARE @search NVARCHAR(400) = 'a cat'  

SELECT t.Item  
FROM fnSplit(@text, ' ') t
join fnSplit(@search, ' ') s
on t.Item = s.Item

the server I am working on is 2008 so I could not test the function — Chit Khine, Jun 26 '18 at 04:50

score 0 · Answer 2 · answered Jun 25 '18 at 07:38

You can try below code hopefully it will help you.

Declare @InputString Varchar(50) = 'a cat'  --<-- String comming in

Declare @Table TABLE (Strings Varchar(50))  --<-- String in the Database
Insert Into @Table Values ('this is a cat')

-- Convert to XML Input sting
declare @xml xml = N'<root><r>' + replace(@InputString, ' ','</r><r>') +     '</r></root>';


WITH DBString AS (

-- Split string stored in the database

SELECT  RTRIM(LTRIM(Split.a.value('.', 'VARCHAR(100)'))) Strings 
FROM   
(SELECT Cast ('<X>' + Replace(Strings, ' ', '</X><X>') + '</X>' AS XML) AS Data
FROM    @Table
) AS t CROSS APPLY Data.nodes ('/X') AS Split(a) )
,InputStrings AS
(

-- Split String coming in the parameter

select RTRIM(LTRIM(r.value('.','varchar(max)'))) as InputString
from @xml.nodes('//root/r') as records(r)
 )

-- Finally Compare the splitted  strings word by word

select 1 from (
SELECT COUNT(1) t1, (SELECT count(1) 
          FROM InputStrings) t2
FROM InputStrings
WHERE EXISTS (SELECT 1 
          FROM DBString
          WHERE InputStrings.InputString = DBString.Strings)
         ) TBF where tbf.t1=TBF.t2

Term Matching using SQL

2 Answers2