Search patterns from SQL consecutive rows within a column

Question

I have a table of text like below, is there any one simple SQL query can find ID 3 (or 3 to 5) from keyword pattern like "a master degree". Thanks?

ID    Words   
1      He  
2      has   
3      a  
4      master  
5      degree  
6      in   
7      University  
8      of  
9      South  
10     India  

Answer 1

This may not be as clean as you like. But you can create a split procedure and then use that. Below is some code from http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx.

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))        
returns @temptable TABLE (items varchar(8000))        
as        
begin        
    declare @idx int        
    declare @slice varchar(8000)        

    select @idx = 1        
        if len(@String)<1 or @String is null  return        

    while @idx!= 0        
    begin        
        set @idx = charindex(@Delimiter,@String)        
        if @idx!=0        
            set @slice = left(@String,@idx - 1)        
        else        
            set @slice = @String        

        if(len(@slice)>0)   
            insert into @temptable(Items) values(@slice)        

        set @String = right(@String,len(@String) - @idx)        
        if len(@String) = 0 break        
    end    
return        
end  

Now, you can have a query like this:

select * from table where words in (select items from dbo.split('a master degree',' '))

Answer 2

Q: is there any one simple SQL query

A: No. It is possible, but not in easy terms...I beleive patindex can be used to identify the first word by the spacing and then loop through each word. Not really simple atleast

If the pattern is 'a master degree', it would be easier to pick out the first word and last word and get the ID column from that and assume the words between must exist. If the input could be 'has degree university' this would be significantly more complicated.

Answer 3

I am assuming that the id are sequential. If not add an identity column to the table and use it in the query.

Here is the complete SQL. I have tried it and it works

CREATE TABLE [dbo].[Words](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Word] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Words] PRIMARY KEY CLUSTERED ([ID] ASC)
)

CREATE FUNCTION [dbo].[DoesPatternMatch]
(
    @p_ID int
)
RETURNS int
AS
BEGIN
    declare @result int
    DECLARE @word2 varchar(50)
    DECLARE @word3 varchar(50)

    SELECT @word2 = word
    from Words
    where ID = @p_ID + 1

    SELECT @word3 = word
    from Words
    where ID = @p_ID + 2

    if (@word2 = 'master' and @word3 = 'degree')
        set @result = 1
    else
        set @result = 0

    return @result
END

select id
from Words
where Word = 'a' and dbo.DoesPatternMatch(ID) = 1

Answer 4

A relatively simple query, assuming that the phrase being searched has three words and that the IDs will always be consecutive (so 'University of South' would return results, but 'University South India' would not):

select t1.id
from wordtable t1
join wordtable t2 on t1.id + 1 = t2.id
join wordtable t3 on t1.id + 2 = t3.id
where t1.words + ' ' + t2.words + ' ' + t3.words = @Phrase;

(SQLServer syntax)

Answer 5

You don't say RDBMS.

The following works in SQL Server 2008

;WITH searchterms as
(
SELECT display_term, occurrence 
FROM sys.dm_fts_parser('"a master degree"',1033,NULL,0)
)

SELECT MIN(ID) AS StartRange, MAX(ID) AS EndRange
FROM data 
JOIN searchterms on data.Words =searchterms.display_term
GROUP BY ID-occurrence
HAVING COUNT(*) = (SELECT COUNT(*) FROM searchterms)

Returns

StartRange  EndRange
----------- -----------
3           5

The sys.dm_fts_parser bit could be replaced with a split function in previous versions that returns the following

display_term                   occurrence
------------------------------ -----------
a                              1
master                         2
degree                         3