Advanced Searching - 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

SQL Server Development (2000)

Advanced Searching

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-04-07 : 07:43:43

Nik writes "I really need help with a multiple keyword search problem.

I have a search page with a drop down menu containing names and values.
Also a keyword field called "key"

This page feeds another page containing conditional statements:

If Request.Form("select") = "section1" then
response.redirect "section1_results.asp?key=" & Request.Form("key")
end if

If Request.Form("select") = "section2" then
response.redirect "section2_results.asp?key=" & Request.Form("key")
end if

The results pages contain SQL along these lines:

SELECT FROM tblDetails
WHERE details LIKE 'varKey'

the varKey is defined as Request.QueryString("key").

My problem is that the search works fine for a single word/ letter entry but
not for multiple words. I want the results to handle the string in a way that
searches for the individual words seperately."

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-04-07 : 09:19:03

You could pass the different words as a comma separated string, and parse the string with the following UDF:


CREATE FUNCTION fnTableFromCSV
(
	@theString varchar(1000),
	@separator char(1)
)
RETURNS @Values TABLE (value INT)
AS
BEGIN
	DECLARE @seppos INT
	DECLARE @curval VARCHAR(1000)

	SET @theString = @theString + @separator
	WHILE PATINDEX('%' + @separator + '%' , @theString) <> 0
		BEGIN
			SELECT @seppos =  PATINDEX('%' + @separator + '%' , @theString)
			SELECT @curval = LEFT(@theString, @seppos - 1)
			INSERT @Values VALUES (@curval)

			SELECT @theString = STUFF(@theString, 1, @seppos, '')
		END
	RETURN
END

I think you get the idea

[edit]

forgot to credit graz for the function...

[/edit]

Edited by - Peter Dutch on 04/07/2003 09:19:44

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources