WHERE CLAUSE LOOKING FOR A VALUE ACROSS MULTIPLE FIELDS

WHERE clause to check if any of three fields contains any of two words.
Is this rephrasing of the question acceptable?
    If yes, then I agree with the above post by knightEknight, http:#37264999 

Yes, I'd agree the rephrasing by hnasr to "WHERE clause to check if any of three fields contains any of two words" is there a way to change the title of the question?

It's sad there is not a more robust solution to a query like this, aka one that does not just continue to add 'OR's' but so be it. Thanks for the assistance.

Hi,

yep, there is, but it takes a little bit more programming.

I made a UDF which is able to find the number of occurances in a given text limited by an optional delimiter (default space). Here it is:

-- =============================================
-- Author:		Christian Coppes
-- Create date: 2011/12/11
-- Description:	Searches for a number of text in
--				other texts
-- =============================================
CREATE FUNCTION fnValMatch
(
	@strSearchWords	AS	nvarchar(500),
	@strSearchText	AS	nvarchar(MAX),
	@strDelimiter	AS	char(1) = ' '
)
RETURNS decimal(20,0)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @intScore		AS bigint;
	DECLARE @intPosition	AS bigint;
	DECLARE @intStartPos	AS bigint;
	DECLARE @tblWords		AS TABLE(strWord		nvarchar(MAX));
	DECLARE @tblSearchWords	AS TABLE(strSearchWord	nvarchar(MAX));
	DECLARE @strInsertWord	AS nvarchar(4000);

	IF ISNULL(@strSearchWords,'') = '' RETURN 0;	-- no words to search
	IF ISNULL(@strSearchText,'')  = '' RETURN 0;	-- no text to search in
	
	-- Split the search text into words table with delimiter character
	SET @intStartPos = 0;
	SET @intPosition = CHARINDEX(@strDelimiter,@strSearchText,1);
	IF @intPosition = 0
		INSERT INTO @tblWords (strWord) VALUES (@strSearchText); -- no occurence of delimiter, complete text
	ELSE
	BEGIN
		WHILE @intPosition > 0
		BEGIN
			SET @strInsertWord = SUBSTRING(@strSearchText,@intStartPos,@intPosition - @intStartPos);
			IF ISNULL(@strInsertWord,@strDelimiter)<>@strDelimiter
				INSERT INTO @tblWords (strWord) VALUES (@strInsertWord);
			SET @intStartPos = @intPosition + 1;
			SET @intPosition = CHARINDEX(@strDelimiter,@strSearchText,@intPosition + 1)
		END -- WHILE
		IF @intStartPos < LEN(@strSearchText)
		BEGIN
			INSERT INTO @tblWords (strWord) VALUES (SUBSTRING(@strSearchText,@intStartPos,LEN(@strSearchText) - @intStartPos + 1))
		END -- IF
	END -- IF

	-- Split the search words into words table with delimiter character
	SET @intStartPos = 0;
	SET @intPosition = CHARINDEX(@strDelimiter,@strSearchWords,1);
	IF @intPosition = 0
		INSERT INTO @tblSearchWords (strSearchWord) VALUES (@strSearchWords);
	ELSE
	BEGIN
		WHILE @intPosition > 0
		BEGIN
			SET @strInsertWord = SUBSTRING(@strSearchWords,@intStartPos,@intPosition - @intStartPos);
			IF ISNULL(@strInsertWord,@strDelimiter)<>@strDelimiter
				INSERT INTO @tblSearchWords (strSearchWord) VALUES (@strInsertWord);
			SET @intStartPos = @intPosition + 1;
			SET @intPosition = CHARINDEX(@strDelimiter,@strSearchWords,@intPosition + 1)
		END -- WHILE
		IF @intStartPos < LEN(@strSearchWords)
		BEGIN
			INSERT INTO @tblSearchWords (strSearchWord) VALUES (SUBSTRING(@strSearchWords,@intStartPos,LEN(@strSearchWords) - @intStartPos + 1))
		END -- IF
	END -- IF

	SELECT @intScore = SUM(Score)
	  FROM
		(SELECT (CASE WHEN strWord LIKE '%' + RTRIM(LTRIM(strSearchWord)) + '%' THEN 1 ELSE 0 END) AS Score
		   FROM @tblSearchWords, @tblWords) AS S;

	RETURN CAST(@intScore AS decimal(20,0));
END

Select allOpen in new window

The function splits the two text strings at their delimiter and inserts them into two temp tables. Then it creates a cross join between the two tables and a score value if the search word is in the search text.

The advantage of this method is that you can use it in any SELECT command, so it is very flexible.

For demonstration, I made a view from the AdventureWorks demo database:

CREATE VIEW [Sales].[viewNEWCustomer]
WITH SCHEMABINDING 
AS
SELECT     i.CustomerID, c.Title, c.FirstName, c.MiddleName, c.LastName, c.Suffix, c.Phone, c.EmailAddress, c.EmailPromotion, a.AddressLine1, a.AddressLine2, a.City, 
                      sp.Name AS StateProvinceName, a.PostalCode, cr.Name AS CountryRegionName, i.Demographics
FROM         Sales.Individual AS i INNER JOIN
                      Person.Contact AS c ON c.ContactID = i.ContactID INNER JOIN
                      Sales.CustomerAddress AS ca ON ca.CustomerID = i.CustomerID INNER JOIN
                      Person.Address AS a ON a.AddressID = ca.AddressID INNER JOIN
                      Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID INNER JOIN
                      Person.CountryRegion AS cr ON cr.CountryRegionCode = sp.CountryRegionCode

Select allOpen in new window

This creates around 18,000 records.

Now you can use the UDF to search not only for one but also for multiple words in multiple columns at the same time like this (here for the columns "LastName" and "AddressLine1"):

WITH qryMatch AS
(
SELECT LastName, AddressLine1,dbo.fnValMatch('ross march beau boul street',CAST(LastName + ' ' + AddressLine1 AS nvarchar(MAX)),' ') AS Score
FROM Sales.viewNEWCustomer
)
SELECT LastName, AddressLine1,Score FROM qryMatch
WHERE Score > 0
ORDER BY Score DESC

Select allOpen in new window

You will get the results with the most found hits at the top of the list. This query searches for the occurance of the partial strings "ross", "march", "beau", "boul" and "street" in the two columns "LastName" and "AddressLine1".

It returns 885 records in 11 seconds running on a little SQL Server in a virtual machine on my computer (using one core of a quattro). So this should be fast enough in a better environment.

Maybe it can be a possibility to add an index to the view consisting of all search columns you want to use to improve performance a little bit.

Cheers,

Christian