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?
 
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
 
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
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.