Quoted literal strings won't force a phrase match

Hello all,
From what I've read, SQL Server is supposed to do a phrase match when
you do a full text search that contains quoted literal strings. So,
for example, if I did a full text search on the phrase "time out" and
I put it in quotes, it's supposed to search for the full phrase "time
out" and not just look for rows that contain the words "time" or
"out." However, this isn't working for me.
Here is the query that I'm using :
SELECT *
FROM Content_Items ci
INNER JOIN FREETEXTTABLE(Content_Items, hed, '"time out"') AS ft
ON ci.contentItemId = ft.[KEY]
ORDER BY ft.RANK DESC
What's it's doing is this : it's returning a bunch of rows that have
the words "time" or "out" in the column called hed. It's also
returning rows that have the full phrase "time out", but it's giving
those rows the same rank as rows that only contain the word "time."
In this case, that rank is 180.
Is there anything else I should be doing in my query, or is there some
configuration option I should have turned on?
Thanks.
Ok, I've made some progress on this problem. Apparently SQL Server is
ignoring noise words in my phrase match.
For example, I ran this query :
SELECT *
FROM Content_Items ci
INNER JOIN FREETEXTTABLE(Content_Items, hed, '"time capsule"') AS ft
ON ci.contentItemId = ft.[KEY]
ORDER BY ft.RANK DESC
And it did exactly what it was supposed to do, since neither "time"
nor "capsule" is a noise word.
My impression was that noise words aren't stripped out of a full text
search if the search phrase is a quoted literal. Thus, my search for
"time out" should look for the full phrase "time out", and not just
the word "time."
Does anybody know why SQL Server is removing my noise word from the
phrase match?
On Jan 18, 12:49 pm, Afrobla...@.gmail.com wrote:
> Hello all,
> From what I've read, SQL Server is supposed to do a phrase match when
> you do a full text search that contains quoted literal strings. So,
> for example, if I did a full text search on the phrase "time out" and
> I put it in quotes, it's supposed to search for the full phrase "time
> out" and not just look for rows that contain the words "time" or
> "out." However, this isn't working for me.
> Here is the query that I'm using :
> SELECT *
> FROM Content_Items ci
> INNER JOIN FREETEXTTABLE(Content_Items, hed, '"time out"') AS ft
> ON ci.contentItemId = ft.[KEY]
> ORDER BY ft.RANK DESC
> What's it's doing is this : it's returning a bunch of rows that have
> the words "time" or "out" in the column called hed. It's also
> returning rows that have the full phrase "time out", but it's giving
> those rows the same rank as rows that only contain the word "time."
> In this case, that rank is 180.
> Is there anything else I should be doing in my query, or is there some
> configuration option I should have turned on?
> Thanks.
|||A noise word is always a noise word. Noise words are applied to the
building of the index, so the full-text search has nothing to find.
Therefore, if you change the noise word list, you must rebuild the index
before you can search for the former noise word. (It is common to run with
either a single blank or a single nonsense word in the noise word file, so
as to get no noise words.)
Of course, you can do a string search for '%time out%' in addition to the
full-text query.
RLF
"Lepidopterist" <jeremypollack@.gmail.com> wrote in message
news:19bd6a5a-c6b0-486b-a69a-45fc1d5b9e92@.f47g2000hsd.googlegroups.com...
> Ok, I've made some progress on this problem. Apparently SQL Server is
> ignoring noise words in my phrase match.
> For example, I ran this query :
> SELECT *
> FROM Content_Items ci
> INNER JOIN FREETEXTTABLE(Content_Items, hed, '"time capsule"') AS ft
> ON ci.contentItemId = ft.[KEY]
> ORDER BY ft.RANK DESC
> And it did exactly what it was supposed to do, since neither "time"
> nor "capsule" is a noise word.
> My impression was that noise words aren't stripped out of a full text
> search if the search phrase is a quoted literal. Thus, my search for
> "time out" should look for the full phrase "time out", and not just
> the word "time."
> Does anybody know why SQL Server is removing my noise word from the
> phrase match?
> On Jan 18, 12:49 pm, Afrobla...@.gmail.com wrote:
>

Quotation Marks in SQL Server

I have an ASP.Net page that allows people to type in strings and store them into a SQL Server DB; which in turn gets displayed on a website.

The project has an admin side that can add/delete/edit announcements, which get displayed on an intranet site. These announcements can be clicked on to display further detail. When announcements are clicked on a javascript popup window is generated that displays the strings. All data is stored in a SQL Server DB.

What I need to know is: how do I check to see if a string has a quotation mark or apostrophe in it so that I can replace it with the appropriate HTML code? (Though it seems I can't display an apostrophe, even when using the HTML code ''')

If I store the string as was entered by the administrator (with quotation marks instead of '"'), the popup window will not display.

Try the links below for all the info you need including how to enable QUOTED_IDENTIFIER option in your create database statement and the restrictions. Hope this helps.

http://msdn2.microsoft.com/en-US/library/ms174393.aspx

http://msdn2.microsoft.com/en-US/library/ms176027.aspx

|||

Search the forums for:

A) Parameterized SQL Query (What you should do)

B) SQL String concatenation (What you are probably doing)

C) SQL Injection attack (The security problems of doing B instead of A)

|||

Motley:

Search the forums for:

A) Parameterized SQL Query (What you should do)

B) SQL String concatenation (What you are probably doing)

C) SQL Injection attack (The security problems of doing B instead of A)

Not worried about SQL Injection attacks. This is an intranet app.|||

Caddre:

Try the links below for all the info you need including how to enable QUOTED_IDENTIFIER option in your create database statement and the restrictions. Hope this helps.

http://msdn2.microsoft.com/en-US/library/ms174393.aspx

http://msdn2.microsoft.com/en-US/library/ms176027.aspx

Can you enable the Quoted_Identifier only when you create a new table?|||You can do it in your create database statement or create table, the how for table is covered in the second link. Hope this helps.|||After reading the replies and links that were posted, I feel that I need to reiterate my question.

There is a page that displays records from a SQL Server DB. These records are "announcements" on an internal bulletin board. An admin has a special page that gives the admin the ability to edit, add or delete any of these records.

For instance, an admin can add an announcement (using a textbox) that says, 'This sentence has "Quotation Marks" in it'. I want to be able to search that specific phrase for the quotation marks and replace them with the appropriate code so that they may be displayed on the bulletin board. I don't want the admin to have to type double quotes or double apostrophes in order for them to show up.

The page needs to be user friendly with any concates or alterations to occur server side.

So if anyone can tell me the proper way of say

if str.chars(x) = "<quotation>" then ...

that would be much appreciated.

This is the javascript that generates the pop-up window:
Alert Descrip

<script language="javascript">
//popup function which recieves the email group description and name as parameters
function popitup3(description, name)
{
newwindow2=window.open('','name','height=400,width=600,scrollbars=yes');
var tmp = newwindow2.document;
tmp.write('<html><head><title>Alert Description</title>');
tmp.write('</head><body><font face="verdana, tahoma, sans-serif" size="2"');
tmp.write('b><br><p align="justify">');
tmp.write(description);
tmp.write('</p><p><a href="javascript:self.close()">close</a> this window.</p>');
tmp.write('</body></html>');
tmp.close();
}
</script>

Variable description is where the text with the quotes would most likely be.|||

I am sorry I did not understand your original post you are looking for ANSI SQL LIKE and Pattern search. Try the link below for sample code. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_la-lz_115x.asp