CODEWITHSUNDEEP
sqlsql-serversearch
Orna Oren
Orna Oren

Reputation: 41

Find Sequence Words SQL

I am using SQL Server 2008 and I need to search a full sentence in a Words table.

Words

ID          LineNum     WordText
----------- ----------- -----------
1           1           i
2           1           love
3           2           i
4           2           love
5           2           ice
6           3           i
7           3           love
8           3           dogs
9           3           too

If the sentence is 'i love dogs', the result in this case should be ID 6-8 only.

ID          LineNum     WordText
----------- ----------- ------------
6           3           i
7           3           love
8           3           dogs

Upvotes: 4

Views: 209

Answers (3)

SelvaS
SelvaS

Reputation: 2125

Here I am splitting the given word into column and stored into a table. And counting the words by using count with partition by and joining with the main table by matching the count of words of the given text.

I have used this answer for splitting the text into table column.

 declare @commavalue varchar(50)='I love dogs'
 declare @table1 table(id int identity(1,1), wordtext varchar(30))

 insert into @table1

 select q2.value from
 (
   SELECT cast('<x>'+replace(@commavalue,'','</x><x>')+'</x>' as xml) 
   as Data
 ) q1 

 CROSS APPLY

 (
   SELECT x.value('.','varchar(100)') as value 
   FROM Data.nodes('x') as f(x)
 ) q2

declare @table table(id int identity(1,1), linenum int, wordtext varchar(30))

insert into @table values( 1, 'i' )
insert into @table values( 1, 'love')
insert into @table values( 2, 'i' )
insert into @table values( 2, 'love')
insert into @table values( 2, 'ice')
insert into @table values( 3, 'i')
insert into @table values( 3, 'love' )
insert into @table values( 3, 'dogs')
insert into @table values( 3, 'too' )

select t.* from
(
 select t1.linenum, 
 count(t1.wordtext) over(partition by t1.linenum order by t1.id) wordCount
 from @table t1  
 join @table1 t2 on t1.wordtext = t2.wordtext
)
 wc
 join @table t on wc.linenum = t.linenum
 join @table1 t1 on t.wordtext = t1.wordtext
where wordcount = (select count(1) from @table1)

Upvotes: 0

Raging Bull
Raging Bull

Reputation: 18737

Try this:

SELECT T1.linenum, 
       word = STUFF((
          SELECT ' ' + T2.wordtext
          FROM TableName T2
          WHERE T1.linenum = T2.linenum
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM TableName T1
GROUP BY T1.linenum
ORDER BY T1.linenum

Result:

LINENUM     WORD
----------------------------
1           i love
2           i love ice
3           I love dogs too

See result in SQL Fiddle.

EDIT:

For result as a list, this is the best I could come up with:

WITH CTE AS 
(SELECT T1.linenum
    , word = STUFF((
          SELECT ' ' + T2.wordtext
          FROM TableName T2
          WHERE T1.linenum = T2.linenum
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM TableName T1
GROUP BY T1.linenum)
SELECT T3.*
FROM CTE JOIN
TableName T3 ON CTE.linenum=T3.linenum
WHERE CTE.word LIKE '%I love dogs%'

Result:

ID  LINENUM WORDTEXT
6   3       I
7   3       love
8   3       dogs
9   3       too

Sample result in SQL Fiddle.

Upvotes: 3

Felix Pamittan
Felix Pamittan

Reputation: 31879

You need a splitter function for this. Read Jeff Moden's article for one of the fastest splitter there is.

First, you want to concatenate WordText with the same LineNum to produce the sentence and check if a the concatenated WordTexts contains the @sentence parameter. Then you only get the WordTexts from the Words table that has the words in the @sentence.

DECLARE @sentence VARCHAR(8000) = 'i love dogs'
DECLARE @delimiter CHAR(1) = ' '

;WITH CTE AS(
    SELECT 
        w1.LineNum,
        Sentence = STUFF((
            SELECT ' ' + w2.WordText
            FROM Words w2
            WHERE w1.LineNum = w2.LineNum
            FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 
        1, 1, '')
    FROM Words w1
    GROUP BY w1.LineNum
)
SELECT w.*
FROM CTE c
INNER JOIN Words w
    ON w.LineNum = c.LineNum
INNER JOIN dbo.DelimitedSplit8K(@sentence, @delimiter) d
    ON d.Item = w.WordText
WHERE c.Sentence LIKE '%' + @sentence + '%'
ORDER BY w.ID

SQL Fiddle

RESULT

ID          LineNum     WordText
----------- ----------- ----------
6           3           i
7           3           love
8           3           dogs

Upvotes: 1

Related Questions