Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 querying

Author  Topic 

ms65g
Constraint Violating Yak Guru

497 Posts
Posted - 2010-05-08 : 21:03:52
Hi,
I familiar with SQL and T-SQL but this query is so difficult for my knowledge, and then I need your helps.
I want to search a phrase in table and return the book and page that contains the phrase.
I save information of a word like book id, page id and position (at page) like this:
DECLARE @t TABLE 
(word varchar(15),
 pos int,
 book int,
 page int,
 UNIQUE(book, page, pos));
 
 INSERT INTO @t
 SELECT 'SQL', 13, 1, 2 UNION ALL
 SELECT 'SQL', 25, 1, 2 UNION ALL
 SELECT 'SQL', 15, 2, 2 UNION ALL
 SELECT 'is', 15, 1, 2 UNION ALL
 SELECT 'is', 16, 1, 2 UNION ALL
 SELECT 'not', 17, 1, 2 UNION ALL
 SELECT 'simple', 30, 1, 2 UNION ALL
 SELECT 'simple', 18, 1, 2 UNION ALL
 SELECT 'SQL', 10, 2, 101 UNION ALL
 SELECT 'simple', 30, 2, 101 UNION ALL
 SELECT 'is', 21, 2, 101 UNION ALL
 SELECT 'not', 22, 2, 101

pos-> 15 16 17 18
……….. SQL is not simple………..
All words of phrase must be in same book id and page id and the positions must be sequence.
If the phrase be ‘SQL is not simple’ then my desire result should be: - Book = 1 and Page = 2

I know following method, but this approach is not dynamic and is not suitable for my scenario.

SELECT t1.book, t1.page
from (select * from @t where word='SQL')d1
join (select * from @t where word='is')d2 on d1.book=d2.book and d1.page=d2.page and d1.pos=d2.pos-1
join (select * from @t where word='not)d3 on d2.book=d3.book and d2.page=d3.page and d2.pos=d3.pos-1
join (select * from @t where word='simple')d4 on d3.book=d4.book and d3.page=d4.page and d3.pos=d4.pos-1

malpashaa
Constraint Violating Yak Guru

264 Posts
Posted - 2010-05-09 : 02:17:32
Try something like this:

/*
IF OBJECT_ID('dbo.CharIndexes', 'U') IS NOT NULL
   DROP TABLE dbo.CharIndexes
GO

CREATE TABLE dbo.CharIndexes
(
   char_index SMALLINT NOT NULL PRIMARY KEY CHECK(char_index BETWEEN 1 AND 8000)
);

WITH DigitsCTE(digit) AS
(
   SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
   SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
)
INSERT INTO dbo.CharIndexes(char_index)
   SELECT TOP(8000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
     FROM DigitsCTE AS D0, DigitsCTE AS D1, DigitsCTE AS D2, DigitsCTE AS D3;
*/

DECLARE @phrase VARCHAR(8000);
SET @phrase =  'SQL is not simple';

DECLARE @PhraseWords TABLE
(
   word VARCHAR(15) NOT NULL,
   word_order SMALLINT NOT NULL,
   PRIMARY KEY(word, word_order)
);

INSERT INTO @PhraseWords(word, word_order)
   SELECT SUBSTRING(' ' + @phrase + ' ', I1.start_offset, I2.end_offset - I1.start_offset),
          ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
     FROM (SELECT I1.char_index + 1 AS start_offset
             FROM dbo.CharIndexes AS I1
            WHERE I1.char_index <= LEN(@phrase) + 2
              AND SUBSTRING(' ' + @phrase, I1.char_index, 1) = ' ') AS I1
          CROSS APPLY
          (SELECT TOP(1) I2.char_index AS end_offset
             FROM dbo.CharIndexes AS I2
            WHERE I2.char_index >= I1.start_offset
              AND I2.char_index <= LEN(@phrase) + 2
              AND SUBSTRING(' ' + @phrase + ' ', I2.char_index, 1) = ' '
            ORDER BY I2.char_index) AS I2;

DECLARE @word_count INT;
SET @word_count = (SELECT COUNT(*)
                     FROM @PhraseWords);

WITH CTE AS
(
   SELECT T.book, T.page, MIN(T.pos) AS pos_min, MAX(T.pos) AS pos_max, COUNT(T.pos) AS pos_count
     FROM (SELECT T.book, T.page, T.pos, T.word, PW.word_order, ROW_NUMBER() OVER(PARTITION BY T.book, T.page ORDER BY T.pos) AS row_num
             FROM @t AS T
                  INNER JOIN
                  @PhraseWords AS PW
                  ON T.word = PW.word) AS T
    WHERE T.word_order = T.row_num
    GROUP BY T.book, T.page
)
SELECT book, page
  FROM CTE
 WHERE pos_count = pos_max - pos_min + 1
   AND pos_count = @word_count;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-05-09 : 05:05:58
you mean you phrase should come as it is without any intermediate words in book page? then as per your sample data no output will come as your bookpage has two consecutive is coming

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts
Posted - 2010-05-09 : 06:43:13
quote:
Originally posted by visakh16

you mean you phrase should come as it is without any intermediate words in book page? then as per your sample data no output will come as your bookpage has two consecutive is coming

Oh my bad. see this sample data:
DECLARE @t TABLE 
(word varchar(15),
 pos int,
 book int,
 page int,
 UNIQUE(book, page, pos));
 
 INSERT INTO @t
 SELECT 'SQL', 13, 1, 2 UNION ALL
 SELECT 'SQL', 25, 1, 2 UNION ALL
 SELECT 'SQL', 15, 1, 2 UNION ALL
 SELECT 'is', 15, 2, 2 UNION ALL
 SELECT 'is', 16, 1, 2 UNION ALL
 SELECT 'not', 17, 1, 2 UNION ALL
 SELECT 'simple', 30, 1, 2 UNION ALL
 SELECT 'simple', 18, 1, 2 UNION ALL
 SELECT 'SQL', 10, 2, 101 UNION ALL
 SELECT 'simple', 30, 2, 101 UNION ALL
 SELECT 'is', 21, 2, 101 UNION ALL
 SELECT 'not', 22, 2, 101
 SELECT * FROM @T
 /*
 word            pos         book        page
--------------- ----------- ----------- -----------
SQL             13          1           2
SQL             25          1           2
SQL             15          1           2
is              15          2           2
is              16          1           2
not             17          1           2
simple          30          1           2
simple          18          1           2
SQL             10          2           101
simple          30          2           101
is              21          2           101
not             22          2           101

(12 row(s) affected)
*/


@malpashaa
Good attempt. but your query give me a empty set
Go to Top of Page

malpashaa
Constraint Violating Yak Guru

264 Posts
Posted - 2010-05-09 : 07:03:06
I am sorry. Try this query instead of the old one(other parts remain the same):

WITH CTE AS
(
   SELECT T.book, T.page, MIN(T.pos) AS pos_min, MAX(T.pos) AS pos_max, COUNT(T.pos) AS pos_count
     FROM (SELECT T.book, T.page, T.pos, T.word, PW.word_order, ROW_NUMBER() OVER(PARTITION BY T.book, T.page ORDER BY T.pos) - PW.word_order AS grp
             FROM @t AS T
                  INNER JOIN
                  @PhraseWords AS PW
                  ON T.word = PW.word) AS T
    GROUP BY T.book, T.page, T.grp
)
SELECT book, page, pos_count
  FROM CTE
 WHERE pos_count = pos_max - pos_min + 1
   AND pos_count = @word_count;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-05-10 : 13:52:43
[code]
DECLARE @t TABLE
(word varchar(15),
pos int,
book int,
page int,
UNIQUE(book, page, pos));

INSERT INTO @t
SELECT 'SQL', 13, 1, 2 UNION ALL
SELECT 'SQL', 25, 1, 2 UNION ALL
SELECT 'SQL', 15, 1, 2 UNION ALL
SELECT 'is', 15, 2, 2 UNION ALL
SELECT 'is', 16, 1, 2 UNION ALL
SELECT 'not', 17, 1, 2 UNION ALL
SELECT 'simple', 30, 1, 2 UNION ALL
SELECT 'simple', 18, 1, 2 UNION ALL
SELECT 'SQL', 10, 2, 101 UNION ALL
SELECT 'simple', 30, 2, 101 UNION ALL
SELECT 'is', 21, 2, 101 UNION ALL
SELECT 'not', 22, 2, 101

DECLARE @Phrase varchar(100)

SET @Phrase = 'SQL is not simple'
SELECT book,page
FROM
(
SELECT t.book,t.page,
STUFF((SELECT ' ' + word FROM @t WHERE book = t.book AND page = t.page ORDER BY pos FOR XML PATH('')),1,1,'') AS Sentence
FROM (SELECT DISTINCT book,page FROM @T)t
)m
WHERE Sentence LIKE '%' + @Phrase + '%'

output
-------------------------------------------------
book page
1 2
2 101

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts
Posted - 2010-05-10 : 14:37:11
quote:
Originally posted by visakh16


DECLARE @t TABLE 
(word varchar(15),
 pos int,
 book int,
 page int,
 UNIQUE(book, page, pos));
 
 INSERT INTO @t
 SELECT 'SQL', 13, 1, 2 UNION ALL
 SELECT 'SQL', 25, 1, 2 UNION ALL
 SELECT 'SQL', 15, 1, 2 UNION ALL
 SELECT 'is', 15, 2, 2 UNION ALL
 SELECT 'is', 16, 1, 2 UNION ALL
 SELECT 'not', 17, 1, 2 UNION ALL
 SELECT 'simple', 30, 1, 2 UNION ALL
 SELECT 'simple', 18, 1, 2 UNION ALL
 SELECT 'SQL', 10, 2, 101 UNION ALL
 SELECT 'simple', 30, 2, 101 UNION ALL
 SELECT 'is', 21, 2, 101 UNION ALL
 SELECT 'not', 22, 2, 101
 
 DECLARE @Phrase varchar(100)
 
 SET @Phrase	= 'SQL is not simple'
 SELECT book,page
 FROM
 (
 SELECT t.book,t.page,
 STUFF((SELECT ' ' + word FROM @t WHERE book = t.book AND page = t.page ORDER BY pos FOR XML PATH('')),1,1,'') AS Sentence
 FROM (SELECT DISTINCT book,page FROM @T)t
 )m
 WHERE  Sentence LIKE '%' + @Phrase + '%'

output
-------------------------------------------------
book	page
1	2
2	101



No. I was not looking for this.
words must be in sequence order also need to not exist any gap between the positions (that contains the words) also ....

I mean first row is selected and second row is not selected:

Row1
BookPage (1, 2)
Position -->
         1      2       3      4
Words  -->
        SQL    is       not    simple

Row2
BookPage (1, 3)
Position -->
          1      2       9     10
Words   -->
          SQL    is      not    simple

(Note: are gaps between 2 adn 9)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-05-10 : 14:52:13
[code]DECLARE @t TABLE
(word varchar(15),
pos int,
book int,
page int,
UNIQUE(book, page, pos));

INSERT INTO @t
SELECT 'SQL', 13, 1, 2 UNION ALL
SELECT 'SQL', 25, 1, 2 UNION ALL
SELECT 'SQL', 15, 1, 2 UNION ALL
SELECT 'is', 15, 2, 2 UNION ALL
SELECT 'is', 16, 1, 2 UNION ALL
SELECT 'not', 17, 1, 2 UNION ALL
SELECT 'simple', 30, 1, 2 UNION ALL
SELECT 'simple', 18, 1, 2 UNION ALL
SELECT 'SQL', 10, 2, 101 UNION ALL
SELECT 'simple', 30, 2, 101 UNION ALL
SELECT 'is', 21, 2, 101 UNION ALL
SELECT 'not', 22, 2, 101

DECLARE @Phrase varchar(100)

SET @Phrase = 'SQL is not simple'
;with cte
as
(
select t.book,t.page,cast(t.word as varchar(1000)) as word,t.pos
from @t t
OUTER APPLY (SELECT TOP 1 word
FROM @t
WHERE book=t.book
AND page=t.page
AND pos = t.pos-1)t1
WHERE t1.word IS NULL
UNION ALL
SELECT t.book,t.page,cast(c.word + ' ' + t.word as varchar(1000)),t.pos
FROM CTE c
JOIN @t t
ON t.book=c.book
AND t.page=c.page
AND t.pos = c.pos+1
)

select book,page from cte
where word like '%'+ @phrase + '%'

output
--------------------------
book page
1 2

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site