WHERE CLAUSE LOOKING FOR A VALUE ACROSS MULTIPLE FIELDS

I'm sure this question has been asked before but I just don't know what to call it to search for it.

So, this where clause currently works, although tiresome :( I'm wondering what the syntax is to streamline this.

WHERE INHOUSE_ADDL_SIG_1 LIKE '%TUBE%' OR INHOUSE_ADDL_SIG_2 LIKE '%TUBE%' OR INHOUSE_ADDL_SIG_3 LIKE '%TUBE%'

something else that comes to mind and would be a bit more complex would be a query where you wanted to check two items across multiple fields, for instance say you wanted to find TUBE or FLEX in those three fields, what would that look like.

Thanks
markloessiAsked:
Who is Participating?
 
knightEknightConnect With a Mentor Commented:
more "OR"s ...

WHERE INHOUSE_ADDL_SIG_1 LIKE '%TUBE%'  OR  INHOUSE_ADDL_SIG_2 LIKE '%TUBE%'  OR  INHOUSE_ADDL_SIG_3 LIKE '%TUBE%'
      OR  INHOUSE_ADDL_SIG_1 LIKE '%FLEX%'  OR  INHOUSE_ADDL_SIG_2 LIKE '%FLEX%'  OR  INHOUSE_ADDL_SIG_3 LIKE '%FLEX%'
0
 
hnasrCommented:
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 
0
 
markloessiAuthor Commented:
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?
0
 
markloessiAuthor Commented:
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.
0
 
BitsqueezerCommented:
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

Open 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

Open 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

Open 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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.