Confusing CONTAINS behavior

I'm experimenting with the CONTAINS operation and I've come across some
confusing or inconsistent results. The following results are exactly the
same if the search terms are enclosed in double quotes so that they are
treated as a phrase.
I'm searching a Books table for books whose Author column contains "cs".
The following CONTAINS statements in the WHERE clause result in books by
"Lewis, C.S." being returned:
CONTAINS( Author, 'cs' )
CONTAINS( Author, 'CS' )
CONTAINS( Author, 'C.S.' )
The fact that "cs" matches an author containing "C.S." indicates that both
case and punctuation are ignored. However, the following CONTAINS statement
does not return any results for "Lewis, C.S.":
CONTAINS( Author, 'c.s.' )
This puzzles me. It seems as though either punctuation is ignored or case
is ignored, but not both. What kind of behavior is that? Is this by design
?Greg Smalter wrote:
> I'm experimenting with the CONTAINS operation and I've come across
> some confusing or inconsistent results. The following results are
> exactly the same if the search terms are enclosed in double quotes so
> that they are treated as a phrase.
> I'm searching a Books table for books whose Author column contains
> "cs".
> The following CONTAINS statements in the WHERE clause result in books
> by "Lewis, C.S." being returned:
> CONTAINS( Author, 'cs' )
> CONTAINS( Author, 'CS' )
> CONTAINS( Author, 'C.S.' )
> The fact that "cs" matches an author containing "C.S." indicates that
> both case and punctuation are ignored. However, the following
> CONTAINS statement does not return any results for "Lewis, C.S.":
> CONTAINS( Author, 'c.s.' )
> This puzzles me. It seems as though either punctuation is ignored or
> case is ignored, but not both. What kind of behavior is that? Is
> this by design?
According to BOL, a word is one or more characters without spaces or
punctuation. Punctuation marks such as the period, colon, semicolon,
comma, and hyphen are ignored during a search.
I do not believe the full-text engine is indexing the punctuation marks
which is why the second query fails.
David Gugick
Quest Software
www.quest.com|||I agree about the specification of the word and how it shouldn't contain
punctuation. That is why I also tested it with phrases surrounded by double
quotes, where the specification doesn't mention that puncutation is not
allowed.
I imagine you are right about the indexing, but that doesn't explain why
CONTAINS( Author, 'C.S.' ) works while CONTAINS( Author, 'c.s.' ) does not
work, because they both have the exact same punctuation. The only differenc
e
is case.
"David Gugick" wrote:

> Greg Smalter wrote:
> According to BOL, a word is one or more characters without spaces or
> punctuation. Punctuation marks such as the period, colon, semicolon,
> comma, and hyphen are ignored during a search.
> I do not believe the full-text engine is indexing the punctuation marks
> which is why the second query fails.
>
> --
> David Gugick
> Quest Software
> www.quest.com
>|||Greg Smalter wrote:
> I agree about the specification of the word and how it shouldn't
> contain punctuation. That is why I also tested it with phrases
> surrounded by double quotes, where the specification doesn't mention
> that puncutation is not allowed.
> I imagine you are right about the indexing, but that doesn't explain
> why CONTAINS( Author, 'C.S.' ) works while CONTAINS( Author, 'c.s.'
> ) does not work, because they both have the exact same punctuation.
> The only difference is case.
That part is confusing...
David Gugick
Quest Software
www.quest.com

Confused with primary keys and foreign keys

Hi, I am currently doing a database project for my company. The database contains 5 tables inside, let's say A,B,C,D,E. The primary key of table A is referenced by all other tables. If an instance is created in other tables except in table A, it means that the foreign keys are not null. However, if an instance is created in table A, it doesn't necessarily mean that the primary key of that particular instance must be referenced by another instance in other tables. In other words, the foreign keys in other tables have null value.

Now, the question is how can I display this primary key in table A together with primary keys of other tables since some of them may not even exist yet? By displaying all the primary keys, I can therefore check that the primary keys in other tables still don't exist and with this, I can input a value to it.

P.S. I am connecting the database with VB 2005 Express Edition and I always get blank value when I tried to retrieve the value.

Thank you very much for the help

Foreign Key:

Is the advanced check constraint which will check the set of values from the another table. The another/reference table is called parent/master table & the current table is called child/detailed table. The Master table which holds the set of value should be not null unique. (you can’t have the more than one Parent for single Child). But your child table column can hold NULL value (which is not yet born)

Primary key:

Is the table levelconstraint which will ensure your table columns have a unique & not null values.

Display the Parent & child values (which is not yet born):

You have to use OUTER JOIN to achieve this. If you post more information we can provide the rite information to you.

|||

The basic problem is that IF there is not a row in the Child table (table with FK), or there is a row, but its FK value is NULL, there is no way to 'connect' the row to the Parent table (table with PK).

It is possible to 'simulate' the missing rows by using a LEFT JOIN, and referring to the Parent table first.

Try out this sample code to see how to combine tables where both tables have matching values, and also where one table is missing a FK value from the other table.

Code Snippet

--***********************************
--Problem: Demonstrate PK-FK JOINS

CREATE TABLE Parent
( RowID int IDENTITY PRIMARY KEY,
MyValue varchar(20)
)
GO

CREATE TABLE Child
( RowID int IDENTITY PRIMARY KEY,
ParentID int REFERENCES Parent(Rowid),
MyValue varchar(20)
)
GO

SET NOCOUNT ON

INSERT INTO Parent VALUES ( 'Parent1' )
INSERT INTO Parent VALUES ( 'Parent2' )
INSERT INTO Parent VALUES ( 'Parent3' )
INSERT INTO CHILD VALUES ( 2, 'Child1' )
INSERT INTO CHILD VALUES ( 3, 'Child2' )
INSERT INTO CHILD VALUES ( 2, 'Child3' )
INSERT INTO CHILD VALUES ( NULL, 'Child4' )

-- To find ONLY Parents AND their Children
SELECT
ParentID = p.RowID,
ParentValue = p.MyValue,
ChildID = c.RowID,
ChildValue = c.MyValue
FROM Parent p
JOIN Child c
ON p.RowID = c.ParentID

-- To find All Parents and Children (if Any)
SELECT
ParentID = p.RowID,
ParentValue = p.MyValue,
ChildID = c.RowID,
ChildValue = c.MyValue
FROM Parent p
LEFT JOIN Child c
ON p.RowID = c.ParentID

-- To find Parents without Children
SELECT
ParentID = p.RowID,
ParentValue = p.MyValue,
ChildID = c.RowID,
ChildValue = c.MyValue
FROM Parent p
LEFT JOIN Child c
ON p.RowID = c.ParentID
WHERE c.ParentID IS NULL

-- To find Children without Parents (Orphans)
SELECT
ParentID = p.RowID,
ParentValue = p.MyValue,
ChildID = c.RowID,
ChildValue = c.MyValue
FROM Parent p
RIGHT JOIN Child c
ON p.RowID = c.ParentID
WHERE c.ParentID IS NULL

DROP TABLE Child
DROP TABLE Parent

|||Thanks for both your suggestions, but I don't have any idea how to perform LEFT JOIN on 5 tables. Can you gimme some idea on this? Thanks..|||

Manivannan.D.Sekaran wrote:

Foreign Key:

Is the advanced check constraint which will check the set of values from the another table. The another/reference table is called parent/master table & the current table is called child/detailed table. The Master table which holds the set of value should be not null unique. (you can’t have the more than one Parent for single Child). But your child table column can hold NULL value (which is not yet born)

Primary key:

Is the table level constraint which will ensure your table columns have a unique & not null values.

Display the Parent & child values (which is not yet born):

You have to use OUTER JOIN to achieve this. If you post more information we can provide the rite information to you.

The child values are actually not null, they are only not yet born. However, I need to display both the parent table which already has a child and which has no child at all. I will try to use OUTER JOIN in the code and see how the result will be. Thanks a lot.

Confused about CONTAINS and CONTAINSTABLE

I have not been using FTS for long, so maybe I am missing an obvious
this here... I have a table with titles of products in them. Its about
a million rows. If I do:
set @.arg='("pda") and ("case")'
select stockcode, rank,
from containstable(product,*,@.arg, 1000)
order by rank desc
and
select stockcode from exp_product where contains(*,@.arg)
I get 20 results for the first and 25 for the second. If I do a
regular like query that returns 25 results. Why does the
containstable not return all the results?
The products titles are less than 100 characters and I did a full FTS
rebuild before the test (and it had finished).
Rob Chafer
Silverfrost
Hi Rob,
Could be something to do with three character searches that are
automatically excluded from the search. If you can change pda to
something longer and test, it might show different results.
I'm just trying to work around this one myself.
Ryan
|||Sorry, disregard that, I had an issue with quoting.
I notice that you are not using wildcards, are you after exact matches
only? Could you try;
set @.arg='("*pda*") and ("*case*")'
Ryan
|||Ryan
Thanks for your reply. It was actually only an example and the words
(specifically) were 'acer' and 'expansys' -- longer than 3 letters. I
did not use wildcards -- the words were in the titles and as single
words so did not use wildcards.
"Ryan" <ryano@.lightsdown.com> wrote:

> Sorry, disregard that, I had an issue with quoting.
> I notice that you are not using wildcards, are you after exact matches
> only? Could you try;
> set @.arg='("*pda*") and ("*case*")'
> Ryan
Rob Chafer
Silverfrost
|||This is abnormal. How many results does this return?
select stockcode, rank, from containstable(product,*,@.arg)
I take it you are using SQL 2000? Also what sp.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Robert Chafer" <noreply@.silverfrost.com> wrote in message
news:uefth1tj1nel2a2e45j890aokpgp6b7bk6@.4ax.com...
> I have not been using FTS for long, so maybe I am missing an obvious
> this here... I have a table with titles of products in them. Its about
> a million rows. If I do:
> set @.arg='("pda") and ("case")'
> select stockcode, rank,
> from containstable(product,*,@.arg, 1000)
> order by rank desc
> and
> select stockcode from exp_product where contains(*,@.arg)
> I get 20 results for the first and 25 for the second. If I do a
> regular like query that returns 25 results. Why does the
> containstable not return all the results?
> The products titles are less than 100 characters and I did a full FTS
> rebuild before the test (and it had finished).
> --
> Rob Chafer
> Silverfrost
|||Robert,
I can explain this... Basically, you're using two very different methods
(CONTAINSTABLE vs. CONTAINS) with the former results limited to the
Top_N_by_RANK where N is 1000. Specifically, you should review KB article
240833 "FIX: Full-Text Search Performance Improved via Support for TOP" at
http://support.microsoft.com//defaul...b;EN-US;240833 for more
details.
Basically, you're not comparing apples-to-apples... Try the below modified
code (removed 1000)
set @.arg='("pda") and ("case")'
select stockcode, rank,from containstable(product,*,@.arg) order by rank desc
-- vs.
select stockcode from exp_product where contains(*,@.arg)
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Robert Chafer" <noreply@.silverfrost.com> wrote in message
news:uefth1tj1nel2a2e45j890aokpgp6b7bk6@.4ax.com...
>I have not been using FTS for long, so maybe I am missing an obvious
> this here... I have a table with titles of products in them. Its about
> a million rows. If I do:
> set @.arg='("pda") and ("case")'
> select stockcode, rank,
> from containstable(product,*,@.arg, 1000)
> order by rank desc
> and
> select stockcode from exp_product where contains(*,@.arg)
> I get 20 results for the first and 25 for the second. If I do a
> regular like query that returns 25 results. Why does the
> containstable not return all the results?
> The products titles are less than 100 characters and I did a full FTS
> rebuild before the test (and it had finished).
> --
> Rob Chafer
> Silverfrost
|||Hilary
Thank you for your help...from your example I realised what the issue
was. Although the recordset returned only has 20 and 25 rows, there
was an extra condition (to select language). Although the condition
was identical in both examples, the ,1000 in the containstable meant
that a few of the matches never made it through.
I live and learn
Thanks again.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote:

> This is abnormal. How many results does this return?
> select stockcode, rank, from containstable(product,*,@.arg)
> I take it you are using SQL 2000? Also what sp.
Rob Chafer
Silverfrost

Confirming the right hyperlink

Hi,
We create rdl files which contains <Hyperlink> elements. The
value for hyperlink is an expression build using report parameters,
field parameters etc and also contains calls to custom assemblies
eg : <Hyperlink>=Parameters!Param1.Value + CustomAssemblyCall + Fields!
someField.Value<Hyperlink>
When this report is rendered by HTML Rendering extension(by specifying
HTML4.0 in the format string passed to render() method) this
<Hyperlink> element is converted to href attribute of the <a> tag.
We have a scenario where we want to test whether this href url
generated is correct or no without deploying the rdl file on the
report server.
Is there any way to pass the xml file and find out what would be href
value when the <Hyperlink>
element is parsed by the report server ?
Any report server dll's which can directly be used ?
Thanks in advance,Can anyone guide me on this