Database Searching

[Magiaus]

Right now, I have a simple search that uses a StoredProcedure with a param varchar(300) to find Products. I pass in a user input string %string%, and it fetchs what comes up to a datagrid. Is there a better method? Maybe RegExp? I'm asking in regards to only SQL Server, unless you know there is just a better way that is SQL92 compliant or something.

CREATE ....
@SearchKey varchar(300)
AS
    SELECT * FROM Products WHERE ProductName Like @SearchKey

I know this should be in the DB forum but they never seem to answer me......

[hellswraith]

I don't know any better ways.

You could include a drop down next to the search box so they can specify a Exact, Starts With, Ends With, or Contains search.

This would be a more complex procedure, but executions would speed up more if a user selects Exact or Starts With. I would default the drop down to Exact so most times the user will select that.

I don't know, I am just talking to talk right now...lol.

[Magiaus]

Yeah, I like to talk myself. Maybe I will make an advanced search option that expose the extra stuff. I have to later add more tables in as well. So I will need a drop down to handle that I imagine... I might could make some really funky old school style JOIN like

Select tblA.*, tblB.* tblC* FROM tblA, tblB, tblC WHERE tblA.field LIKE @SearchKey OR tblB.field LIKE @SearchKey OR tblC.field LIKE @SearchKey

but that would confuse me and would it even work...? maybe... but it would be bloated

[dj4uk]

You didn't say which database you are using - if it is MS SQL Server then you can add FULLTEXT indexes to tables in the database that can give you alot more flexibility when searching.

DJ

EDIT: OK you said SQL server - I'm ain't awake yet!

[Magiaus]

huh..sleep...zzzzz..zzzzzz..inedxes. ...zzzzzzzzzz

Doesn't that mostly effect the speed at witch results are fetched? I can't remember what I was taught about using indexes. You order you caloums in the order of what is most frequently used as a key or read. You make them indexes if it is needed. I'm just not sure when it is needed, and when it isn't.

I don't know the SQL Syntax for all that anymore. I went from DB2 and C-Tree to Access and from Access to SQL Server. Access made me lazy..... real lazy. I barely remember any SQL at all. I mean I can SELECT, UPDATE and all, but I need to reread all the syntax for Create Table and so on. I don't even know all the SQL Server DataTypes yet.

[dj4uk]

Fulltext indexing is different to standard indexing. It has various functions built in that allow better search methods within a table. For example Boolean queries without the need to build the sql statement manually. To googling it or get a good SQL book!

It might be quicker and more powerful than reinventing the wheel and writing a search procedure yourself.

DJ