Search with multiple words - 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 2008 Forums

Transact-SQL (2008)

Search with multiple words

Author

Topic

alexellice
Starting Member

13 Posts

Posted - 2012-04-04 : 13:04:41

Hi all..

I'm building a new website that will have many thousands of records in a database. The problem I have is that I'm going to need to be able to use multiple search queries at once..

Example:

John Blackwell is in the night club

I will remove "is, in and the" but I still need to find hits with John, John Blackwell, night, club and nightclub.

The webhotel does not support full text search either :-( So any suggestions here pls ????

(The search would be carried out in the description field only.. so no need for multiple tables or databases.)

Forgot to say that I'm using MS sql server 2008, asp.net 4 and c#

X002548
Not Just a Number

15586 Posts

Posted - 2012-04-04 : 13:19:09

Well it's really just

John, Blackwell, night, club and nightclub

Right

Do you mean you need top parse a string a look for thos values?

I would create a table udf that parses the string and would join to with a like

SELECT Parameter, yourDescription FROM yourTable
INNER JOIN dbo.udf_Table
ON yourDescription LIKE '%' + Parameter + '%'



CREATE FUNCTION [dbo].[udf_Table](@ParmList varchar(8000), @Delim varchar(20))
RETURNS @table TABLE 
(Parameter varchar(255))

AS 

/*
 SELECT * FROM dbo.udf_Table( 'a|~|b|~|c', '|~|')
*/

BEGIN
	DECLARE @x int, @Parameter varchar(255)

	WHILE CHARINDEX(@Delim, @ParmList)-1 > 0
	  BEGIN 
		INSERT INTO @table(Parameter) 
		SELECT SUBSTRING(@ParmList,1,CHARINDEX(@Delim, @ParmList)-1)
		

		SELECT @ParmList  = SUBSTRING(@ParmList,CHARINDEX(@Delim, @ParmList)+LEN(@Delim), LEN(@ParmList)-CHARINDEX(@Delim,@ParmList))
	  END 
	INSERT INTO @table(Parameter) SELECT @ParmList
	RETURN
END

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

alexellice
Starting Member

13 Posts

Posted - 2012-04-04 : 15:14:32

right.. thankyou for such a quick response... I'n not good with sql. But I'll give this a go..

I ran (installed) the function you wrote but how to I access it now?

example:

From my c# I will send a query to the Db... Something like :
SELECT * FROM Gallery WHERE imgDescr LIKE @_search

what do I do with what you gave me?

X002548
Not Just a Number

15586 Posts

Posted - 2012-04-04 : 15:55:03

ummmm..the syntax is in my first post...by since you gave me more info

SELECT Parameter, imgDescr FROM Gallery
INNER JOIN dbo.udf_Table(@_searck,' ')
ON imgDescr LIKE '%' + Parameter + '%'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

alexellice
Starting Member

13 Posts

Posted - 2012-04-04 : 16:41:28

ok... I ran a test.. but its not working right?

this is my test query

DECLARE @_search varchar;
set @_search = 'janni'

SELECT Parameter, imgDescr FROM LH_Gallery
INNER JOIN dbo.udf_Table(@_search,' ')
ON imgDescr LIKE '%' + Parameter + '%'

it only selects (or searches) for the first letter (j)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-04 : 18:48:11

here's another version

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-04-04 : 19:44:28

quote:
Originally posted by alexellice

ok... I ran a test.. but its not working right?

this is my test query

DECLARE @_search varchar;
set @_search = 'janni'

SELECT Parameter, imgDescr FROM LH_Gallery
INNER JOIN dbo.udf_Table(@_search,' ')
ON imgDescr LIKE '%' + Parameter + '%'

it only selects (or searches) for the first letter (j)

Dude ( or Dudette), that's only because your declare is for a 1 byte value

Do this

DECLARE @_search varchar(255);

and Try

set @_search = 'janni is a chick e'

Should give you matches on

%janni% -- any janni matches
%is% -- any matches for is, for example this
%a% -- anything with an a in it
$chick% -- anything matching chick
%e% -- anything with an e

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-04-04 : 19:47:25

quote:
Originally posted by visakh16

here's another version

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I wonder how much faster that is

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

alexellice
Starting Member

13 Posts

Posted - 2012-04-05 : 02:33:12

ok... GREAT... that really helped (I did say that I'm not good at SQL!)

I used this

DECLARE @_search varchar(255);

set @_search = 'rasmus caroline'

SELECT Parameter, imgDescr FROM LH_Gallery
INNER JOIN dbo.udf_Table(@_search,' ')
ON imgDescr LIKE '%' + Parameter + '%'

Problem is that it looks for "rasmus" and then only "carolin" (missing the last letter).. if I try any suggests why?

actually, after further testing..

If I write "Sofie Rasmus og Mille" it will search for "Sofie" and "rasmus og mill" (missing last letter + not seperating words)

I'm really happy that we're getting somewhere with this now... and really appreciate all your help.

I should also mention that I want to return the results, when it works correctly as a table with all the fields included, not just the ones searched in

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-04-08 : 18:27:27

quote:
Originally posted by X002548

quote:
Originally posted by visakh16

here's another version

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I wonder how much faster that is

Brett

It's likely not that much faster because it still uses a WHILE loop. Check out the following article for performance curves and a seriously improved method for splitting strings.
http://www.sqlservercentral.com/articles/Tally+Table/72993/

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources