keywords context summary

I use fts to query a sql server 2005 db and the results are displayed
on a web page.
Out of a large text how can a summary be extracted "including" also my
keywords? Like more contextual summary.
google has a fancy way of formating the search results and display a
keyword contextual description for every link in their search
any idea?
thxke
This is difficult. For text and image data you really don't have a good way
other than incorporating indexing services and generating hyperlinks to
seeing the data.
Here is an example of how to do this:
http://www.indexserverfaq.com/SQLhitHighlighting.htm
For small char (typically under 200 bytes) use charindex or patindex. For
larger amounts of data it is more efficient to mark it up client side.
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
"xke" <xkeops@.gmail.com> wrote in message
news:1170996261.834692.202170@.h3g2000cwc.googlegro ups.com...
>I use fts to query a sql server 2005 db and the results are displayed
> on a web page.
> Out of a large text how can a summary be extracted "including" also my
> keywords? Like more contextual summary.
> google has a fancy way of formating the search results and display a
> keyword contextual description for every link in their search
> any idea?
> thxke
>

Keywords

Hi,
I have to build a table for something like 1.000.000 books.
I need to use keywords for each book (to be able to search with the keywords
in an intranet).
I wonder the best solution to achieve this:
*Add a new text field (varchar) and then use Full Text Search index
*Add two tables, one for the keywords and one to join the books' table and
the new keywords one.
Which one of these two solutions is the best with SQL Server ?
Thanks.Hello Lionel,
Disclaimer: I don't have much knowledge on full text search.
I would do the second option. This would allow you to quickly search through
the keywords assuming you have only one word in you table.
Aaron Weiker
http://aaronweiker.com/

> Hi,
> I have to build a table for something like 1.000.000 books.
> I need to use keywords for each book (to be able to search with the
> keywords
> in an intranet).
> I wonder the best solution to achieve this:
> *Add a new text field (varchar) and then use Full Text Search index
> *Add two tables, one for the keywords and one to join the books' table
> and
> the new keywords one.
> Which one of these two solutions is the best with SQL Server ?
> Thanks.
>|||Why don't you get a document management system that can do this job for
a fraction of the cost, 2-3 orders of magnitude faster and which comes
with a query language mean for text searches? SQL was never meant for
this kind of data.|||Well, do you have a name of a programmable document manager under Microsoft
and IIS (for an intranet) ?
Whatever, SQL server should be (as Oracle do) able to index text.
And if my boss wants to keep this solution, i still don't know if my
solution is the best issue: the keywords in a separate table, and then a
third one to join the books with the keywords.
Thanks for answring and taking time.

Keyword seach (Not full Phrase) parameterise sql statement

hi,

i want to do search by keywords for e.g "John Smith". should search for "John" and "Smith"

it is easy to do it using dynamic sql statement.

but i am using parameters sql.

this is my sql

"select * from emp_tbl where fname like '%' + @.keyw + '%' or lname like '%' + @.keyw + '%' "

the above sql will search by full phrase

how can i make it search each word in the phrase.

aslo, i am searching for 70-551 exam. to upgrade my mcad to mcts.

can anybody help.

Hi,

The solution is not that difficult. Just do one thing before sending the parameter to the stored procedure. Just replace the white spaces with %, so your query will become something like this

"select * from emp_tbl where fname like '%' + John%Smith + '%' or lname like '%' + John%Smith + '%' "

I am sure it will work for you.

Thanks and best regards,

|||

Hi,

it is not working!!!!.

can anybody advice how to do.

"Dynamic SQL IS EASY. BECUASE I CAN JUST SPLIT THE STRING AND BUILD MY SQL ACCORDINGLY"

|||

Hi,

Also check in Sql Profiler if the values received to the sp are correct as it always works perfectly fine with me. Make sure that the values received by the sp does not contain whitespaces or any non required character.

Thanks and best regards,

|||

hi,

i think you have a records like that

1. John Smith

2. John William Smith

so, in that case it will work perfect. because the records has both the words "John" and "Smith" so the '%' in between will ignore the word "William". this is how it works.

but if you have a records like that

1. John Smith

2. John William

3. Smith Graham.

in this case it will not work.

i need to bring all the records that has the words "John" or "Smith" with a single keyword string "John Smith"

|||

Hi,

You were right, actually I misunderstood. In order to achive your task you have to manipulate your query in a way so that it will look somthing like this

Select * From tbl_User Where FirstName in ('John','Smith') or LastName in ('John','Smith')

If you think you can achive this task easily from your stored procedure well and good, otherwise you can send the whole query from your application and execute it from the stored procedure.

Hope now it will help you out.

Thanks and best regards,

|||

Hi,

your idea is nice. but i shall make my sql like this

Select * From tbl_User Where FirstName in ('%John%','%Smith%') or LastName in ('%John%','%Smith%')

because in need a like %

what you suggested i have to pass exact name

i will check and i will let you know.

thanks for your help

|||

Hi Hussain,

Well I have also tried this way but it didn't gave the required result but the query which I mentioned worked.

Thanks and best regards,

|||

Hey Hussain,

It seems like I have sorted out the problem write following query instead of with IN keyword.

Select * From tbl_User Where FirstName + ' ' + LastName LIKE '%John%Smith%'

Hope it will work with you as well. Happy Coding ;)

Thanks and best regards,

|||

Hi,

again your query will returns the rows like the following

John Smith

John William Smith

but it will not return rows that begin with

Smith

William John

William Smith

i am trying to query in one field suppose you have in the firstName Column the following values

1. John Smith

2. Smith

3. William Smith

4. John William

5. Smith Wiliam

6. XYZ John

7. hjkdfjhkjdfhkjfh smith ashdsjakdhjkh

so your query will not returns all the rows that has either John or Smith

anyway i have solved. and this is my solutions

this is the function i have created

Create

FUNCTION [dbo].[udf_SearchEachWord](@.Stringnvarchar(4000),@.Phrasenvarchar(400))

RETURNS

char(1)

AS

BEGINDECLARE @.INDEXINTDECLARE @.SLICEnvarchar(4000)DECLARE @.ITMES_TABLETABLE(ITEMSNVARCHAR(4000))DECLARE @.FOUNDchar(1)

SET @.FOUND='0'-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z-- ERO FIRST TIME IN LOOPSELECT @.INDEX= 1-- following line added 10/06/04 as null-- values cause issuesWHILE @.INDEX!=0BEGIN-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTERSELECT @.INDEX=CHARINDEX(' ',@.STRING)-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLEIF @.INDEX!=0SELECT @.SLICE=LEFT(@.STRING,@.INDEX- 1)ELSESELECT @.SLICE= @.STRING-- PUT THE ITEM INTO THE RESULTS SETINSERTINTO @.ITMES_TABLE(Items)VALUES(@.SLICE)-- CHOP THE ITEM REMOVED OFF THE MAIN STRINGSELECT @.STRING=RIGHT(@.STRING,LEN(@.STRING)- @.INDEX)-- BREAK OUT IF WE ARE DONEIFLEN(@.STRING)= 0BREAKEND

--================================================================================

SELECT @.INDEX= 1-- following line added 10/06/04 as null-- values cause issuesWHILE @.INDEX!=0BEGIN-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTERSELECT @.INDEX=CHARINDEX(' ',@.Phrase)-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLEIF @.INDEX!=0SELECT @.SLICE=LEFT(@.Phrase,@.INDEX- 1)ELSESELECT @.SLICE= @.Phrase-- PUT THE ITEM INTO THE RESULTS SET

IFEXISTS(SELECT ITEMSFROM @.ITMES_TABLEWHERE ITEMSlike'%'+ @.SLICE+'%')beginSET @.FOUND='1'breakend

-- CHOP THE ITEM REMOVED OFF THE MAIN STRINGSELECT @.Phrase=RIGHT(@.Phrase,LEN(@.Phrase)- @.INDEX)-- BREAK OUT IF WE ARE DONEIFLEN(@.Phrase)= 0BREAKEND

RETURN @.FOUND

END

and this is how i am using it

select

au_fnamefrom authorswhere DBO.udf_SearchEachWord(au_fname,'John Smith')='1'

and this is the results

au_fname

-------

william john

smith john

john smith

smith

william smith

smith william

john william smith

john

(8 row(s) affected)

Keyword Query

I have a sample photo database where we have added keywords to search for photos. I wanted a way to list all of the keywords that are in the database individually. The problem is in my keyword field there are many keywords seperated by a comma.

Ex: "bull, barrel, rodeo, western, cowboy" would in the keyword field for one photo.

I wanted to select distinct all of the individual words from each keyword field in all of the records.

Can this be done? What would the query look like?

I am looking for a list like:

bull
barrel
rodeo
western
cowboy

Any suggestions?

Thanks,
RobCREATE TABLE myTable99 (Photo Id int, Keyword vatchar(256))
GO|||Yes, you can get the distinct keywords from your table. You need to use LOOP to fetch each value of the colomn and assign it to the variable. And then you need to split string based on ",". Insert the seperated keyword into the temporary table. At last, you just do

SELECT DISTINCT Keyword FROM temporary TABLE

to get the distinct keyword.|||Got cut short...

far as I know you need some code...this example would be 1 row from a cursor for example...

USE Northwind
GO

SET NOCOUNT ON

DECLARE @.x varchar(8000), @.y int, @.z int

DECLARE @.tbl table (col1 varchar(8000))

SELECT @.x = 'Brett|No|Rhyme|to|Well', @.y = 1, @.z = CHARINDEX('|',@.x,1)-1

WHILE @.z <> -1
BEGIN
INSERT INTO @.tbl (col1) SELECT SUBSTRING(@.x, @.y, @.z-@.y+1)
SELECT @.y = @.z + 2
SELECT @.z = CHARINDEX('|',@.x,@.y)-1
END

INSERT INTO @.tbl (col1) SELECT SUBSTRING(@.x, @.y, LEN(@.x)-@.y+2)

SELECT LEN(col1), col1 FROM @.tbl
GO

SET NOCOUNT OFF|||Brett, I see what you are doing and I understand what is going on but I don't know how to get my data into where you have 'Brett|No|Rhyme|to|Well'.

My field name is keyword and the table name is TblPhotos and the Database name is CTM_samples. How would I select the keyword values and insert them into the temp
table?

Thanks alot for your explination.|||You'll need a cursor...

do a fetch and assign the columns to variables...

do the loop

then do the insert

See?|||The following query:

SELECT photo,
NullIf(
SubString(',' + keyword + ',' , counter, CharIndex(',' , ',' + keyword + ',' , counter) - counter) , '') AS keywords
FROM photos, stringlen
WHERE counter <= Len(',' + keyword + ',') AND SubString(',' + keyword + ',' , counter - 1, 1) = ','
AND CharIndex(',' , ',' + keyword + ',' , counter) - counter > 0

will return:

photo1 bull
photo1 barrel
photo1 rodeo
photo1 western
photo2 eiffel
photo2 tower
photo2 paris

The key here it create a 'stringlen' table with an counter field with incrementing numbers. So, if your longest keyword column contains 200 characters, then you would have values 1-200 in your counter field to cover the substring manipulation.

keyword / phrase searching

I have to do an app that will contain a lot of string data that will be
keywords and phrases and I will need a very fast search of that material.
Does SQL Server 2005 have any tools that are geared to this kind of search.
I don't think it has to be as fast as google but probably faster than where
clauses using "like" and "in".
Thanks,
TCheck out the Full Text Search features in BooksOnLine.
--
Andrew J. Kelly SQL MVP
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uaSeL83tHHA.3796@.TK2MSFTNGP02.phx.gbl...
>I have to do an app that will contain a lot of string data that will be
>keywords and phrases and I will need a very fast search of that material.
>Does SQL Server 2005 have any tools that are geared to this kind of search.
>I don't think it has to be as fast as google but probably faster than where
>clauses using "like" and "in".
> Thanks,
> T
>