combining many contains()

Hi all,
I have a nvarchar(255) column on a tblKeyword table with many queries in a
verified FTS form. i.e-
"Microsoft Corporation"
("Hewelet Packard") OR HP
Google OR Froogle
Sun
I want to issue a combined FTS query that will gather all (*) information
from the indexed table, based on all existing queries in the tblKeyword
table.
Ie, something like:
SELECT * FROM myftstable
WHERE CONTAINS(*,[("Microsoft Corporation") OR (("Hewelet Packard") OR HP)
OR (Google OR Froogle) OR (Sun)])
Is it possible to do it dynamiclly in some sort? other then connecting all
strings and sending them all to the contains?
Thanks!
Guy,
Yes, there is a way to do this via a stored proc:
use pubs
go
-- DROP PROCEDURE usp_FTSearchPubsInfo
CREATE PROCEDURE usp_FTSearchPubsInfo ( @.vcSearchText varchar(7800))
AS
declare @.s as varchar (8000)
set @.s='select pub_id, pr_info from pub_info where
contains(pr_info,'+''''+@.vcSearchText+''''+')'
exec (@.s)
go
-- returns 2 rows
EXEC usp_FTSearchPubsInfo '("books" and "publisher")'
go
-- Using your example:
EXEC usp_FTSearchPubsInfo '("Microsoft Corporation" or ("Hewelet Packard" or
"HP") or ("Google" or "Froogle") or ("Sun"))'
Regards,
John
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:Oz8p5UXKEHA.3728@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have a nvarchar(255) column on a tblKeyword table with many queries in a
> verified FTS form. i.e-
> "Microsoft Corporation"
> ("Hewelet Packard") OR HP
> Google OR Froogle
> Sun
> I want to issue a combined FTS query that will gather all (*) information
> from the indexed table, based on all existing queries in the tblKeyword
> table.
> Ie, something like:
> SELECT * FROM myftstable
> WHERE CONTAINS(*,[("Microsoft Corporation") OR (("Hewelet Packard") OR HP)
> OR (Google OR Froogle) OR (Sun)])
> Is it possible to do it dynamiclly in some sort? other then connecting all
> strings and sending them all to the contains?
> Thanks!
>
|||Hi John,
I meant how to do it programatically, so that the queries exists in
tblKeyword will be populated automatically as a long (@.vcSearchText varchar)
Guy
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23nWpPQYKEHA.1272@.tk2msftngp13.phx.gbl...
> Guy,
> Yes, there is a way to do this via a stored proc:
> use pubs
> go
> -- DROP PROCEDURE usp_FTSearchPubsInfo
> CREATE PROCEDURE usp_FTSearchPubsInfo ( @.vcSearchText varchar(7800))
> AS
> declare @.s as varchar (8000)
> set @.s='select pub_id, pr_info from pub_info where
> contains(pr_info,'+''''+@.vcSearchText+''''+')'
> exec (@.s)
> go
> -- returns 2 rows
> EXEC usp_FTSearchPubsInfo '("books" and "publisher")'
> go
> -- Using your example:
> EXEC usp_FTSearchPubsInfo '("Microsoft Corporation" or ("Hewelet Packard"
or[vbcol=seagreen]
> "HP") or ("Google" or "Froogle") or ("Sun"))'
> Regards,
> John
>
>
> "Guy Brom" <guy_brom@.yahoo.com> wrote in message
> news:Oz8p5UXKEHA.3728@.TK2MSFTNGP12.phx.gbl...
a[vbcol=seagreen]
information[vbcol=seagreen]
HP)[vbcol=seagreen]
all
>
|||Guy,
I'm not sure what you're asking for here... Could you provide some examples?
Are you looking for a client-side (IE-based) solution or a server-side
(T-SQL based) solution? If the former, you may want to checkout KB article
246800 (Q246800) "INF: Correctly Parsing Quotation Marks in FTS Queries" at:
http://support.microsoft.com//defaul...b;EN-US;246800
Regards,
John
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:OMfprgdKEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi John,
> I meant how to do it programatically, so that the queries exists in
> tblKeyword will be populated automatically as a long (@.vcSearchText
varchar)[vbcol=seagreen]
> Guy
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%23nWpPQYKEHA.1272@.tk2msftngp13.phx.gbl...
Packard"[vbcol=seagreen]
> or
in[vbcol=seagreen]
> a
> information
tblKeyword
> HP)
> all
>
|||John hi,
I need a server-side solution (T-SQL based) for connecting all of the
records appear in tblKeyword into 1 long string. Is it possible?
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OJ%238ruhKEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Guy,
> I'm not sure what you're asking for here... Could you provide some
examples?
> Are you looking for a client-side (IE-based) solution or a server-side
> (T-SQL based) solution? If the former, you may want to checkout KB article
> 246800 (Q246800) "INF: Correctly Parsing Quotation Marks in FTS Queries"
at:[vbcol=seagreen]
> http://support.microsoft.com//defaul...b;EN-US;246800
> Regards,
> John
>
> "Guy Brom" <guy_brom@.yahoo.com> wrote in message
> news:OMfprgdKEHA.1312@.TK2MSFTNGP12.phx.gbl...
> varchar)
> Packard"
queries[vbcol=seagreen]
> in
> tblKeyword
OR[vbcol=seagreen]
connecting
>
|||Hi Guy,
Ok. I looked back over your original posting and I didn't realize that you
had two tables - tblKeyword and myftstable - and that in affect you wanted
to "pass" the tblKeyword table values to CONTAINS search_condition clause.
I'm sure there are other ways of doing this, but for now, I've developed two
cursor based solutions - assuming I'm understanding your question properly:
The below examples use the database (pubs) and the FT-enabled table
(authors) and the table (keyword) is your tblKeyword table:
use pubs
go
create table keyword(kword varchar(50))
go
insert into keyword values ('white')
insert into keyword values ('("john" or "paul")')
insert into keyword values ('Yokomoto')
go
select * from keyword
go
-- Simple Cursor Fetch with a CONTAINS statement...
SET NOCOUNT ON
DECLARE keyword_cursor CURSOR FAST_FORWARD
FOR
select kword from keyword
OPEN keyword_cursor
DECLARE @.keyword varchar(50)
FETCH NEXT FROM keyword_cursor INTO @.keyword
WHILE (@.@.fetch_status <> -1)
BEGIN
select * from authors where contains(*,@.keyword)
FETCH NEXT FROM keyword_cursor INTO @.keyword
END
CLOSE keyword_cursor
DEALLOCATE keyword_cursor
SET NOCOUNT OFF
GO
-- Complex Cursor Fetch into a temp table and then select from it...
set nocount on
DECLARE keyword_cursor CURSOR FAST_FORWARD
FOR
select kword from keyword
CREATE TABLE #authors_PK (author_pk char(11))
OPEN keyword_cursor
DECLARE @.keyword varchar(50), @.author_pks char(11), @.sql nvarchar(600)
-- Fetch the first row in the cursor.
FETCH NEXT FROM keyword_cursor INTO @.keyword
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.sql = 'insert into #authors_PK (author_pk) select au_id from
authors where contains(*, ''' + @.keyword + ''')'
exec(@.sql)
FETCH NEXT FROM keyword_cursor INTO @.keyword
END
CLOSE keyword_cursor
DEALLOCATE keyword_cursor
select * from #authors_PK
drop table #authors_PK
go
-- Clean-up
drop table keyword
go
Let me know if this is what you're looking for.
Regards,
John
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:urOPK8jKEHA.3492@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> John hi,
> I need a server-side solution (T-SQL based) for connecting all of the
> records appear in tblKeyword into 1 long string. Is it possible?
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OJ%238ruhKEHA.1340@.TK2MSFTNGP12.phx.gbl...
> examples?
article[vbcol=seagreen]
> at:
varchar(7800))[vbcol=seagreen]
> queries
Packard")
> OR
> connecting
>
|||Exactly!!
Thank you John!
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uwMwhCnKEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi Guy,
> Ok. I looked back over your original posting and I didn't realize that you
> had two tables - tblKeyword and myftstable - and that in affect you wanted
> to "pass" the tblKeyword table values to CONTAINS search_condition clause.
> I'm sure there are other ways of doing this, but for now, I've developed
two
> cursor based solutions - assuming I'm understanding your question
properly:[vbcol=seagreen]
> The below examples use the database (pubs) and the FT-enabled table
> (authors) and the table (keyword) is your tblKeyword table:
> use pubs
> go
> create table keyword(kword varchar(50))
> go
> insert into keyword values ('white')
> insert into keyword values ('("john" or "paul")')
> insert into keyword values ('Yokomoto')
> go
> select * from keyword
> go
> -- Simple Cursor Fetch with a CONTAINS statement...
> SET NOCOUNT ON
> DECLARE keyword_cursor CURSOR FAST_FORWARD
> FOR
> select kword from keyword
> OPEN keyword_cursor
> DECLARE @.keyword varchar(50)
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> select * from authors where contains(*,@.keyword)
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> END
> CLOSE keyword_cursor
> DEALLOCATE keyword_cursor
> SET NOCOUNT OFF
> GO
> -- Complex Cursor Fetch into a temp table and then select from it...
> set nocount on
> DECLARE keyword_cursor CURSOR FAST_FORWARD
> FOR
> select kword from keyword
> CREATE TABLE #authors_PK (author_pk char(11))
> OPEN keyword_cursor
> DECLARE @.keyword varchar(50), @.author_pks char(11), @.sql nvarchar(600)
> -- Fetch the first row in the cursor.
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.sql = 'insert into #authors_PK (author_pk) select au_id from
> authors where contains(*, ''' + @.keyword + ''')'
> exec(@.sql)
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> END
> CLOSE keyword_cursor
> DEALLOCATE keyword_cursor
> select * from #authors_PK
> drop table #authors_PK
> go
> -- Clean-up
> drop table keyword
> go
> Let me know if this is what you're looking for.
> Regards,
> John
>
>
> "Guy Brom" <guy_brom@.yahoo.com> wrote in message
> news:urOPK8jKEHA.3492@.TK2MSFTNGP09.phx.gbl...
> article
Queries"
> varchar(7800))
> Packard")
>

Combining Fields in a stored procedure

my question is this...I have to fields I want to retreive..One is a nvarchar and the other is an integer...I want to return them in this format
(interger field + '/' + nvarcharfield) as combinedfield
problem is i get errors when I try to get this value
I just need the info I know you cant add theses two together...

Example of output needed...

31/OfficeVisit

my sp

ALTER procedure EncounterCodes_NET
(
@.ClinicID int
)
as
select
CombinedField=(EnCodeID + EnCodeDesc)
from
Clinic_Encodes
Where
ClinicID=@.ClinicID
Order by Sortorderreplace the EncodeID with

Cast(EnCodeID as varchar)

Column max size for String

Hello I currently am using the nvarchar type which has a maximum size of 4000 char's. Is there any other type that I can use which can store a greater amount of char's ?

Many thanks

GrantVarchar can go up to 8000. You can usetext orntext fields which have no practical upper limit on the number of chars. Withtext orntext fields fields you lose some flexibility such as the ability to use thetext orntext field in a where clause.

text is for variable-length non-Unicode data.

ntext is for variable-length Unicode data.|||text datatypes can hold upto 2GB bytes of data ... And ntext also used the same limitation ... The only difference being text datatypes use 1 bytes for 1 character and ntext types use 2 bytes for storing single unicode character ...

collations and nvarchar

Should a programmer bother using nvarchar for a person's name (special chracters allowed) if I still use SQL_Latin1_General_CP1_CI_AS?

I don't think you can get ascent with Varchar because those characters are not ASCII that is the reason Greek and other similar languages comes with separate code pages and collations.

http://msdn2.microsoft.com/en-us/library/ms144250.aspx