contains syntax

[Isaac]

I want to search a table alias doc, column name Result, where doc.Result is full text indexed, using Contains, and I want to find where there is the term BSA and a % sign (percent sign, literally) within 5 words of each other.

Do I have to escape the % sign like [%] ? that seems to come back wrong syntax

This returns zero results but that may be accurate, not sure. the [%] returns syntax error

where contains(doc.Result,'NEAR((BSA, %), 5)')

note - cross posted https://www.sqlservercentral.com/forums/topic/correct-syntax-for-this-contains-statement

 

[pbaldy]

I've never used Contains, or Near for that matter, but in CharIndex the % needs to be delimited, so try that. This works for me in a query:

CharIndex('%', FieldName)

 

[pbaldy]

PS: I tried to test with your syntax, but got an error about not being able to use CONTAINS because the table is not full-text indexed, and I'm too lazy to do that.

And another by-the-way, you'll likely need delimiters on BSA as well.

 

[Isaac]

believe it or not i have precedent code that seems to confirm i don't need to delimit the bsa - but you may be right about the %, got so many quotes going on here. Contains is unbelievable - you could literally use forms of Contains and Near to replace an entire NLP operation

 

[pbaldy]

I should have kept my mouth shut, as I later realized the whole NEAR bit was in quotes, so all bets are off on what might be required inside that.

 

[Isaac]

it's a very strange animal indeed

 

[PamelaLynn]

In SQL Server Full-Text Search, use the `NEAR` operator with the `~` symbol and a number to specify proximity. For your query, try: `CONTAINS(Result, 'BSA NEAR("5") "%"')`. No need to escape the "%" sign. This should search for the desired proximity.

 

[Isaac]

Tilde?

the 2 near terms go together as such

one,two

it only worked with a term, didn't work with the symbol