Lots of WHERE LIKE - 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 2005 Forums

Transact-SQL (2005)

Lots of WHERE LIKE

Author

Topic

markehb
Starting Member

2 Posts

Posted - 2010-02-17 : 07:40:11

Hi,

I've got a table full of records with say 10 fields, running a query such as:

SELECT * FROM table
WHERE FirstName LIKE '%'+@Firstname+'%'
OR Surname LIKE '%'+@Surname+'%'
OR Field3 LIKE '%'+@Field3+'%'
OR Field4 LIKE '%'+@Field4+'%'
OR Field5 LIKE '%'+@Field5+'%'
OR Field6 LIKE '%'+@Field6+'%'
OR Field7 LIKE '%'+@Field7+'%'
OR Field8 LIKE '%'+@Field8+'%'
OR Field9 LIKE '%'+@Field9+'%'
OR Field10 LIKE '%'+@Field10+'%'

from a search form (where you can enter a search term for any of the 10 fields). Problem with this is of course is that entering the letter 'a' in all fields in the search form will likely return most if not all of the records. What I want to do is limit it so it only shows results for those that have 3 or more partial matches?

Does that make sense?

Thanks,
Mark

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-17 : 07:57:54

you probably want to look at FULLTEXT. I think it can do what you want (it can certainly look for the same pattern across multiple columns).

I've never had cause to use it though.

Here's some reading:
http://msdn.microsoft.com/en-us/library/ms345119(SQL.90).aspx

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-17 : 11:20:57

Agreed with T-Charlie about looking into FULLTEXT, cuz those parital matches are terrible performers.. That having been said, here is a method that migth work for you. I'm sure there are some other ways, but this is quick and dirty:

SELECT * 
FROM table
WHERE 
	(
		CASE WHEN FirstName LIKE '%'+@Firstname+'%' THEN 1 ELSE 0 END
		+ CASE WHEN Surname LIKE '%'+@Surname+'%' THEN 1 ELSE 0 END
		+ CASE WHEN Field3 LIKE '%'+@Field3+'%' THEN 1 ELSE 0 END
		+ CASE WHEN Field4 LIKE '%'+@Field4+'%' THEN 1 ELSE 0 END
		+ CASE WHEN Field5 LIKE '%'+@Field5+'%' THEN 1 ELSE 0 END
		+ CASE WHEN Field6 LIKE '%'+@Field6+'%' THEN 1 ELSE 0 END
		+ CASE WHEN Field7 LIKE '%'+@Field7+'%' THEN 1 ELSE 0 END
		+ CASE WHEN Field8 LIKE '%'+@Field8+'%' THEN 1 ELSE 0 END
		+ CASE WHEN Field9 LIKE '%'+@Field9+'%' THEN 1 ELSE 0 END
		+ CASE WHEN Field10 LIKE '%'+@Field10+'%' THEN 1 ELSE 0 END
	) >= 3

markehb
Starting Member

2 Posts

Posted - 2010-02-19 : 05:50:19

Thanks for the advice, i'll take a good look at full text searching. Your code works a treat in the meantime Lamprey, thanks :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources