Extending Word Proximity - SQL Server Forums

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)

Extending Word Proximity

Author

Topic

mori0043
Starting Member

18 Posts

Posted - 2008-06-09 : 09:30:40

Hi, I am trying to write a stored proc that would allow word proximity. I would like to define the proximity of the word so that I could ask for a word1 within 10 of word2.

I am wondering if I could get some suggestions on how to go about writing this? Should I put this in a stored proc? Should I just cursor through each row and then cursor through each word after word1?

I hope that this is clear... Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-09 : 09:39:44

Have you tried FULLTEXT service?

E 12°55'05.25"
N 56°04'39.16"

mori0043
Starting Member

18 Posts

Posted - 2008-06-10 : 09:10:57

Do you mean using the 'NEAR' function? That unfortunately doesn't work because I need to specify the amount of words between. For example:

word1 word2 word3

I would like to specify find word1 within a distance of 3 words of word3 etc...

Any more ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 10:18:22

Try with different values for @Distance.
I have tried for 1, 2 and 3.

DECLARE	@Sample TABLE (ID INT, Data VARCHAR(200))

INSERT	@Sample
SELECT	1, 'This sentence is complete, is it not?' UNION ALL
SELECT	2, 'I think this unneccessary exercise is gibberish.' UNION ALL
SELECT	3, 'This is short.'

DECLARE	@Word1 VARCHAR(20),
	@Word2 VARCHAR(20),
	@Distance INT

SELECT	@Word1 = 'This',
	@Word2 = 'is',
	@Distance = 1

;WITH Yak (ID, RowID)
AS (
	SELECT		s.ID,
			w.RowID
	FROM		@Sample AS s
	CROSS APPLY	dbo.fnParseList(' ', s.Data) AS w
	WHERE		w.Data IN (@Word1, @Word2)
)

SELECT DISTINCT	y1.ID
FROM		Yak AS y1
INNER JOIN	Yak AS y2 ON y2.ID = y1.ID
WHERE		y1.RowID < y2.RowID
		AND y2.RowID - y1.RowID <= @Distance

E 12°55'05.25"
N 56°04'39.16"

mori0043
Starting Member

18 Posts

Posted - 2008-06-10 : 11:05:01

Thanks... I will give this a shot. I am wondering what the performance impact is though? If I where parsing through a million records then would it be slow. I will do some tests. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources