dynamic Boolean searches AND, OR,

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

SQL Server Development (2000)

dynamic Boolean searches AND, OR,

Author

Topic

djchrome
Starting Member

19 Posts

Posted - 2002-07-05 : 15:09:38

Hi, I am looking for an easy and efficiant way to add a boolean search function to an already existing search i have using ASP pages and MSSQL 2000 SPROCS.

Currently I have some code such as:

CREATE PROCEDURE sp_name

@var1 int = NULL,
@var2 varchar(50) = NULL

AS

SELECT blah, blah, blah
FROM sometable INNER JOIN anothertable ON anID = anID
WHERE (table1.column2 = COALESCE(@var1,table1.column2) AND table2.column4 = COALESCE(@var2,table2.column4))

and this works like a charm, if i pass a value into @var1 or @var 2 it gives back results where those crietera match, or if I pass no values in it returns all records.... anyways, you get the idea...

Now I would like to add the ability to introduce a 3rd variable, and have it run a boolean search on a varchar(2500) column. This column is a description field for an image so I would like to be able to search the descriptions by passing in something like 'car OR bike AND travel' and return all the records that have the words 'car' or 'bike' and 'travel' somewhere in the description field.

How can i do this? I am using SQL Server Standard Edition so I don't believe I have full text search functions available, (not sure if that would even help).

Thanks all for your support and guidance!

P.S. i would also like to maintain the functinality so that if all the @var# are blank it still returns all records.

weisseb
Starting Member

8 Posts

Posted - 2002-07-06 : 07:10:56

First of all, have you considered using stored functions (since you're on a SQL 2000 Server), to return your results. It goes more naturally with fx. the SELECT statement, as in SELECT * FROM dbo.fn_name(null, 'car').

Second, see the definition of the CHARINDEX function in Books Online. Perhaps you'll find it useful to find 'AND's and 'OR's.

Third, deside whether or not you will be using operator hierachy, 'car OR bike AND travel' really means 'car OR (bike AND travel)'. And whether or not you will allow the use of parenthesis.

And I do like your use of the COALESCE function :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources