Searching a table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Searching a table

Author

Topic

RoniR
Starting Member

16 Posts

Posted - 2009-12-15 : 09:51:47

hey guys i need ur help one more time
i am trying to search records in my story table..

i want my search to be able to find any words in my keys,that is
suppose i have this text

Egypt awards $95 M oil deal to French Technip
Cairo (RPN) - The government-owned Burullus Gas Company has awarded a $95 million construction contract to France’s Technip to maintain plateau production on its West Delta Deep Marine concession in the Mediterranean...

lets say i want to search Egypt and Techniq at the same time..
what i wrote so far is that procedure

WITH StoryEntries AS (
SELECT ROW_NUMBER() OVER (order by Story.Story_date desc)AS Row,
Story.Story_ID,Story_Title,Story_Teaser,Story_Date,Story_CreationDate,Story_Author,((LEN(Replace(dbo.ScrapeText(Story_Body),' ','')))/5) as Wnb,
story.story_body
FROM Story
where (Story_Title like '%' + @key + '%'
or
Story_Teaser like '%' + @key + '%'
or
Story_Keywords like '%' + @key + '%'
or
Story_Author like '%' + @key + '%'
or
Story_Author = @author
) and
Story.Story_Status = 'published' and
Story_Deleted = 0

)
SELECT row,Story_ID,Story_Title,Story_Teaser,Story_Date,Story_CreationDate,Story_Author,Wnb,story_body
FROM StoryEntries
WHERE Row between (@PageIndex - 1) * @Count + 1 and @PageIndex*@Count

it seems that the like doesnt do exactly what i want
when i search egypt techniq i dont get any results
searching egypt alone works

i would like to search any containing words in my fields..

most probably it has to be a smart search..so basically users might want to build the results upon the search terms they are using..
how can i achieve that
best regards guys,

thanks again
roni

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-12-15 : 09:58:56

Hi,

use the Free Text Search feature of sql server, it will help you to search with more options...

But if u wants to do without it by using normal t-sql, then check the below 2 links, which i gave solutions for one guy for diff. search scenarios..

http://forums.asp.net/p/1339119/3079450.aspx#3079450
http://forums.asp.net/p/1341979/2718373.aspx#2718373

RoniR
Starting Member

16 Posts

Posted - 2009-12-15 : 15:32:27

quote:
Originally posted by ddramireddy

Hi,

use the Free Text Search feature of sql server, it will help you to search with more options...

But if u wants to do without it by using normal t-sql, then check the below 2 links, which i gave solutions for one guy for diff. search scenarios..

http://forums.asp.net/p/1339119/3079450.aspx#3079450
http://forums.asp.net/p/1341979/2718373.aspx#2718373

alright man i will take a look at those
will keep u updated in case i need anything
regards

RoniR
Starting Member

16 Posts

Posted - 2009-12-16 : 04:41:57

i've done some reading and i found this lovely manual here http://www.kodyaz.com/articles/SQL-Server-Full-Text-Search-Indexing.aspx for the Full Text Search

its working now but i still have one problem

when i do this

SELECT * FROM story WHERE CONTAINS(story_title, '"egypt techniq"');

SELECT * FROM story WHERE FREETEXT(story_title, 'egypt techniq');

in the first one i am not getting any results but i do have a title that has egypt and techniq togther and in the second one the freetext i get more then one results
still i do want to get in the top results anything that has egypt techniq together and then everything that has techniq and then techniq
is this doable i cant seem to figure it out
any help would be good
thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources