Expanding SQL Query

Hello,

I’d like some help to expand a query that I use:

Basically this query enables a space delimited string to be passed into it which is then used as a search parameter.  It finds the words not matter which order they appear.

This has been working for a while.  Now there is a requirement to search more than 1 column.

I have tried numerous ways but can’t get this to work correctly.  The best result I have is to create two statements and union them together – which is less than ideal

Here is the code, any suggestions appreciated.

ALTER PROCEDURE [dbo].[Custom_Search_Items]
(
@searchString VARCHAR(512)
)
AS
BEGIN

DECLARE @FilterTable TABLE (Data VARCHAR(512))

 INSERT INTO @FilterTable (Data)
 SELECT DISTINCT S.Data
 FROM fnSplit(' ', @searchString ) S

 SELECT DISTINCT
        T.item_number AS Number,
        T.ver AS Version,
        T.description AS Description,
FROM
      items T
      INNER JOIN @FilterTable F1 ON T.description LIKE '%' + F1.Data + '%' 
      LEFT JOIN @FilterTable F2 ON T.description NOT LIKE '%' + F2.Data + '%'
 WHERE
      F2.Data IS NULL

Open in new window


ALTER FUNCTION [dbo].[ fnSplit] ( @sep CHAR(1), @str VARCHAR(512) )
 RETURNS TABLE AS
 RETURN (
           WITH Pieces(pn, start, stop) AS (
           SELECT 1, 1, CHARINDEX(@sep, @str)
           UNION ALL
           SELECT pn + 1, stop + 1, CHARINDEX(@sep, @str, stop + 1)
           FROM Pieces
           WHERE stop > 0
      )

      SELECT
           pn AS Id,
           SUBSTRING(@str, start, CASE WHEN stop > 0 THEN stop - start ELSE 512 END) AS Data
      FROM
           Pieces
 )

Open in new window


Basically I need it to search the T.item_number field as well as the T.Description field
andyw27Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
You may simply extend the original query but you have to specify how to combine the two columns. If any column must contain the word:
ALTER PROCEDURE [dbo].[Custom_Search_Items]
(
@searchString VARCHAR(512)
)
AS
BEGIN

DECLARE @FilterTable TABLE (Data VARCHAR(512))

 INSERT INTO @FilterTable (Data)
 SELECT DISTINCT S.Data
 FROM fnSplit(' ', @searchString ) S

 SELECT DISTINCT
        T.item_number AS Number,
        T.ver AS Version,
        T.description AS Description,
FROM
      items T
      INNER JOIN @FilterTable F1 ON T.description LIKE '%' + F1.Data + '%'
                                 OR T.item_number LIKE '%' + F1.Data + '%'

Open in new window

If both columns must contain a word from the search string replace the OR operator with  AND.

If the item_number is not of character data type then use appropriate conversion, e.g. CAST(T.item_number AS varchar(20)) etc.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
andyw27Author Commented:
thanks, but for some reason that approach still find results for description, but nothing for item number (where I know it should return results)
0
andyw27Author Commented:
On closer inspection it appears the OR is being treated as a AND.  For example entering a search of '05' will only return results where 05 is present in both the description and item_number fields - very confusing?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why not add the ItemNumber in the same way you did with the Description?
ALTER PROCEDURE [dbo].[Custom_Search_Items]
(
@searchString VARCHAR(512)
)
AS
BEGIN

DECLARE @FilterTable TABLE (Data VARCHAR(512))

 INSERT INTO @FilterTable (Data)
 SELECT DISTINCT S.Data
 FROM fnSplit(' ', @searchString ) S

 SELECT DISTINCT
        T.item_number AS Number,
        T.ver AS Version,
        T.description AS Description,
FROM
      items T
      INNER JOIN @FilterTable F1 ON T.description LIKE '%' + F1.Data + '%' 
      LEFT JOIN @FilterTable F2 ON T.description NOT LIKE '%' + F2.Data + '%'
      INNER JOIN @FilterTable F3 ON T.item_number LIKE '%' + F1.Data + '%' 
      LEFT JOIN @FilterTable F4 ON T.item_number NOT LIKE '%' + F2.Data + '%'
 WHERE
      F2.Data IS NULL

Open in new window

0
pcelbaCommented:
It works for me exactly as I described...

What is the data type of  item_number  column? Could it contain some unexpected characters? Does it allow NULL values?

The OR/AND ambiguity is impossible if you have more complex join expression then you may use parentheses.

You may also place the F1.Data into the output and look what exactly is compared in the LIKE.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.