How do I modify this query to search for the keywords that are only two words apart?

Question

Post reply

How do I modify this query to search for the keywords that are only two words apart?

sweeneyliam71

SSC Rookie

Points: 44
More actions
April 12, 2013 at 11:10 pm

#273337

How do I modify this query to search for the keywords that are only two words apart?
select id, SubjectText from dbo.email where contains
([ContentText],'execute NEAR class near program near code near SQL');
go
These are the results i am getting :
id SubjectText
1 examples for you
3 Oracle classes starting soon
4 Toms Hardware nes letter
5 Registration starts soon
7 Dreamspark SQL order ready
8 SQL classes free online
10 Visual Basic key
20 SQL Help
21 A Free SQL Server Monitoring Package
22 A Monitoring Package

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sweeneyliam71 SSC Rookie Points: 44 More actions · Answer 1

I have tried

select id, SubjectText from dbo.email where contains

([ContentText],'"( (execute near class, near program ,NEAR code,near SQL ))"',2,true);

go

and

select id, SubjectText from dbo.email where contains

([ContentText],'near( (execute , class, program code, SQL ))',2,true);

go

and

select id, SubjectText from dbo.email where contains

([ContentText],' (execute near class near program NEAR code near SQL )',2,true);

go

none of them work they all produce an error PLEASE HELP

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

One, are you using full text indexing, and two it would help if you also provided the full and complete error message you are getting.

sweeneyliam71 SSC Rookie Points: 44 More actions · Answer 3

Yes I am using full text indexing. The error messages I am getting are,depending on which query I use:

Msg 7630, Level 15, State 2, Line 2

Syntax error near '(' in the full-text search condition 'NEAR (execute, class , program, SQL ,code'.

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near '2'.

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 4

sweeneyliam71 (4/13/2013)
I have tried
select id, SubjectText from dbo.email where contains
([ContentText],'"( (execute near class, near program ,NEAR code,near SQL ))"',2,true);
go
and
select id, SubjectText from dbo.email where contains
([ContentText],'near( (execute , class, program code, SQL ))',2,true);
go
and
select id, SubjectText from dbo.email where contains
([ContentText],' (execute near class near program NEAR code near SQL )',2,true);
go
none of them work they all produce an error PLEASE HELP

To the best of my knowledge, FTS does not allow you to actually specify what the proximity limits of what NEAR actually is. In English, they never define what "NEAR" means nor do they allow you to specify it.

If being able to define what NEAR means in your proximity searches, I recommend a more advanced FTS engine offered by 3rd parties. Prepare to open your wallet, though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Calibear Ten Centuries Points: 1377 More actions · Answer 5

I would say try using "like" to the best you can and the different options given there. You could use something to the effect of:

where column like '%word1 %_ _% word2%'

The underscore will make sure there is some kind of value there (a letter may be) and then the spaces will insure that there are letters/words in between. This isn't a perfect solution, but might get you closer to what you are looking for.

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455