Keyword search - 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 2000 Forums

Transact-SQL (2000)

Keyword search

Author

Topic

ibanez
Starting Member

5 Posts

Posted - 2005-02-09 : 17:16:37

Hi I am trying to search a field of keywords that are separated by a space. I am able to search to get one keyword but if the user enters more than one it doesn't work. I originally had it working in ColdFusion but now I have to get it to work with asp.net. Here is what I have and any help would be greatly appreciated.

<code>
SELECT [File_Name], [File_Type], [Document_Date] FROM [NSWCPC] WHERE ([Keywords] LIKE '%' + ? + '%')">
</code>

Thanks!
Ken

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-09 : 17:31:33

I take it Keywords is your space separated list and ? the space separated word that the user enters?
You could use a function in t-sql to seprate the entered words into a tables and join to that.
Better maybe would be to only hold one keyword per row in the database then you could use like against the entered list.

As you are it's probably easiest to separate the expressions in the statement in asp (better to pass them to an SP then you could change structures easily but...)
so it woud become (note use of spaces to prevent toad matching with toadstool).
where ' ' + keywords + ' ' like '% ' + k1 + ' %'
and ' ' + keywords + ' ' like '% ' + k2 + ' %'
and ' ' + keywords + ' ' like '% ' + k3 + ' %'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-02-09 : 17:44:47

Also take a look at the following link. The first article may be helpful.

http://www.sqlteam.com/searchresults.asp?SearchTerms=keyword+search

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-09 : 18:38:40

also read here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45677

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

ibanez
Starting Member

5 Posts

Posted - 2005-02-16 : 17:42:30

Well I appreciate the help, and I am getting further with this BUT I still can't get my select command to work. I have re-written it. Can someone take a look and see what's up with this?

I have also tried this with a loop in there, but I can't get it working right.

<code>
Dim ConnectString, SelectStatement As String
Dim Connect As OleDbConnection = New OleDbConnection
Dim Adapter As OleDbDataAdapter = New OleDbDataAdapter
Dim NSWCPCDS As DataSet = New DataSet
Dim WhereClause As String ' = "Where "

If Not IsPostBack Then
result = CType(Context.Handler, SenderClass)
Dim mystring As String = result.KeyList
'Dim myarray() As String = Split(mystring, " ")
'Dim X As Integer
'For x = 1 To myarray.Length - 1
WhereClause = "WHERE File_Name LIKE '" & mystring & "'"

SelectStatement = "Select File_Name, File_Type, Document_Date From NSWCPC " & WhereClause

Response.Write(SelectStatement)
'Next
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\inetpub\wwwroot\archive\database\NSWCPC-Archive.mdb"
Connect.ConnectionString = ConnectString
Adapter.SelectCommand = New OleDbCommand(SelectStatement, Connect)
Adapter.SelectCommand.Connection.Open()
Adapter.Fill(NSWCPCDS, "NSWCPC")
NSWCPCGrid.DataSource = NSWCPCDS.Tables("NSWCPC")
Page.DataBind()
NSWCPCGrid.Dispose()
</code>

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-16 : 17:45:51

what is the actual selectStatement that you are trying to execute? What does the Response.write(selectStatement) give you?

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

ibanez
Starting Member

5 Posts

Posted - 2005-02-17 : 08:34:04

This is my Select Statement: "Select File_Name, File_Type, Document_Date From NSWCPC " & WhereClause

My Where Clause is this: "WHERE File_Name LIKE '" & mystring & "'"

So together they should be "Select File_Name, File_Type, Document_Date FROM NSWCPC WHERE File_Name LIKE 'textbox.text' <- this being what was entered by the user.

This is what shows up in the response.write: Select File_Name, File_Type, Document_Date From NSWCPC WHERE File_Name LIKE 'sea'

the text in ' ' is what is typed into the textbox.

I got it to work somewhat. If you type the exact filename it works but if you type just a part of it it doesn't.

Thanks,
Ken

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-17 : 08:47:35

that's because ot needs to be like this:
Select File_Name, File_Type, Document_Date From NSWCPC WHERE File_Name LIKE 'sea%' -- or LIKE '%sea%', which eever you prefer.
i must warn you that if you don't have any checks in place a malicious user can drop your whoule database.
that is called sql injection. learn about it.

Go with the flow & have fun! Else fight the flow

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-17 : 08:48:02

Rewrite your string to include % so this
"Select File_Name, File_Type, Document_Date From NSWCPC WHERE File_Name LIKE 'sea'"

Reads like
Select File_Name, File_Type, Document_Date From NSWCPC WHERE File_Name LIKE '%sea%'

Andy

Beauty is in the eyes of the beerholder

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-17 : 08:49:10

Ahhhhhhhhhhhhhhhh SNIPED

Were's that YAK!

Beauty is in the eyes of the beerholder

ibanez
Starting Member

5 Posts

Posted - 2005-02-17 : 15:53:03

Sweet, I got it working, thanks so much for all your help guys!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-17 : 17:21:53

Andy....you mean this?

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-17 : 17:23:56

attaboy

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-17 : 17:26:58

fast learner...

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-17 : 17:31:43

C & M....you guys are just too much! LOL! Thanks a bunch

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-17 : 20:44:35

quote:
Originally posted by Xerxes

Andy....you mean this?

Semper fi, Xerxes, USMC(Ret.)
--------------------------------------------------
Once a Marine ALWAYS a Marine!

That's the bugger, somebody pull that trigger (INSTEAD OF)

Beauty is in the eyes of the beerholder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources