CONTAINS

Hi,
I have a table like this:
CREATE TABLE T1(
C1 int identity(1,1) PRIMARY KEY,
C2 NVARCHAR(50))
INSERT T1(C2) VALUES('test')
INSERT T1(C2) VALUES('Xtest')
INSERT T1(C2) VALUES('testX')
Assuming that C2 is enabled for FTS, this query does not return "Xtest":
SELECT * FROM T1 WHERE CONTAINS (*,'"*test*"')
It returns only "test" and "testX". How can I do that?
Thanks in advance,
Leila
"Leila" <Leilas@.hotpop.com> wrote in message
news:O7ujWH2iFHA.3300@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a table like this:
> CREATE TABLE T1(
> C1 int identity(1,1) PRIMARY KEY,
> C2 NVARCHAR(50))
> INSERT T1(C2) VALUES('test')
> INSERT T1(C2) VALUES('Xtest')
> INSERT T1(C2) VALUES('testX')
> Assuming that C2 is enabled for FTS, this query does not return "Xtest":
> SELECT * FROM T1 WHERE CONTAINS (*,'"*test*"')
> It returns only "test" and "testX". How can I do that?
You can't, unless you use "Xtest" or "Xtest*". FTS doesn't handle suffix
searches, and so the leading * is ignored. Another option would be to fall
back to LIKE when a leading * is used.
Dan
|||I think you mean prefix (comes before) which SQL FTS does not support. SQL
FTS does support suffix (comes at the end) type searches when you use the
wildcard operator in the Contains predicate.
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
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:e1DACM3iFHA.3216@.TK2MSFTNGP10.phx.gbl...
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O7ujWH2iFHA.3300@.TK2MSFTNGP10.phx.gbl...
> You can't, unless you use "Xtest" or "Xtest*". FTS doesn't handle suffix
> searches, and so the leading * is ignored. Another option would be to fall
> back to LIKE when a leading * is used.
> Dan
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23RPqSH4iFHA.3064@.TK2MSFTNGP15.phx.gbl...
>I think you mean prefix (comes before) which SQL FTS does not support. SQL
> FTS does support suffix (comes at the end) type searches when you use the
> wildcard operator in the Contains predicate.
Possibly, I can't remember which way around they go. If it refers to the
string part, it's suffix that isn't supported as the wildcard will be the
prefix. Thanks for pointing out my error though
Dan
|||Thank you all :-)
Then LIKE will be the only poosible way?
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:ek0j$34iFHA.3216@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23RPqSH4iFHA.3064@.TK2MSFTNGP15.phx.gbl...
SQL[vbcol=seagreen]
the
> Possibly, I can't remember which way around they go. If it refers to the
> string part, it's suffix that isn't supported as the wildcard will be the
> prefix. Thanks for pointing out my error though
> Dan
>
|||Unfortunately so.
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
"Leila" <Leilas@.hotpop.com> wrote in message
news:ehN0lR%23iFHA.576@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thank you all :-)
> Then LIKE will be the only poosible way?
>
> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
> news:ek0j$34iFHA.3216@.TK2MSFTNGP10.phx.gbl...
> SQL
> the
the
>

Constraint question

I'm constructing a menu in a SQL Server database.
Each menu can have sub menus. So my table looks like this:

CREATE TABLE menu
(
idINT NOT NULL IDENTITY PRIMARY KEY,
nameVARCHAR(30)NOT NULL,
parentID INTNOT NULL /*ID Of Parent Menu -1 If Root*/
)

IS there a way of placing a constraint on it so if one menu is deleted
all its sub menus get deleted automatically. A normal foreign key
causes a cicrcular problem. Any ideas?Hi

I guess you could have a loop in a trigger

WHILE @.@.ROWCOUNT > 0
BEGIN
DELETE FROM menu
WHERE parentID not in ( SELECT ID FROM menu)
AND ParentID <> 1
END

John

<wackyphill@.yahoo.com> wrote in message
news:1103232008.918252.175160@.f14g2000cwb.googlegr oups.com...
> I'm constructing a menu in a SQL Server database.
> Each menu can have sub menus. So my table looks like this:
> CREATE TABLE menu
> (
> id INT NOT NULL IDENTITY PRIMARY KEY,
> name VARCHAR(30) NOT NULL,
> parentID INT NOT NULL /*ID Of Parent Menu -1 If Root*/
> )
>
> IS there a way of placing a constraint on it so if one menu is deleted
> all its sub menus get deleted automatically. A normal foreign key
> causes a cicrcular problem. Any ideas?|||(wackyphill@.yahoo.com) writes:
> I'm constructing a menu in a SQL Server database.
> Each menu can have sub menus. So my table looks like this:
> CREATE TABLE menu
> (
> id INT NOT NULL IDENTITY PRIMARY KEY,
> name VARCHAR(30) NOT NULL,
> parentID INT NOT NULL /*ID Of Parent Menu -1 If Root*/
> )

Better to let parentID be NULL if root. If you go for -1 you cannot
have an fkey constraint anyway.

> IS there a way of placing a constraint on it so if one menu is deleted
> all its sub menus get deleted automatically. A normal foreign key
> causes a cicrcular problem. Any ideas?

You would have to write a trigger, and skip the constraint. Or simply
do the cascading in the stored procedure that removes a menu.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the info guys.

> Better to let parentID be NULL if root. If you go for -1 you cannot
> have an fkey constraint anyway.
Yeah, good point. Although I was concidering allowing multiple root
menus which is why I did it. Each menu w/ -1 would begin another major
High Level Menu System/Section. And to get a list of all the sections
simply search for the menus w/ a -1 parentID. I thought if things
swelled I'd cut down on the amount of menus that need to be returned in
a query that way. (They will end up being displayed in a tree control
that shows all menu's in the current section).

> You would have to write a trigger, and skip the constraint. Or simply
> do the cascading in the stored procedure that removes a menu.

OK, I'm just learning SQL Server and didn't want to skip over a feature
that would do it for me if there was one. I'll probably go w/ the
stored procedure method. How best to set it up so a database can only
be accessed through its stored procedures, and stop adhoc SQL commands
that would not inforce the cascading?|||Although, now that I think about it I certainly could do the same thing
w/ NULLS as w/ -1s :) Sorry, Wasn't thinking that one thru far enough.|||(wackyphill@.yahoo.com) writes:
> OK, I'm just learning SQL Server and didn't want to skip over a feature
> that would do it for me if there was one. I'll probably go w/ the
> stored procedure method. How best to set it up so a database can only
> be accessed through its stored procedures, and stop adhoc SQL commands
> that would not inforce the cascading?

It is of course not possible to lock out ad-hoc statements completely
from Query Analyzer completely for people with admin privileges.
.. But with judicial use of constraints you can prevent bad things from
happening, at least by mistake.

But for application design, yes, it is a good idea make all access with
through stored procedures, and only grant users access to the stored
procedures, but not directly to the tables.

The advantage of doing the cascading in the stored procedure, is that
you can keep a table constraint that prohibits deletion.

Overall, while cascading referential integrity is available in SQL Server,
there are several situations where it is not possible to use it, the
usefulness of the feature is limited.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp