T-SQL Array Search

neojakey · August 18th, 2010, 05:23 PM

Hello All,

I am hoping one of you guys or gals can help me out here cos I am kinda outta my depth. I took over a web project and I have a search engine to search for products, the user can also select a category to search in. The user could possible type "Duty Call" and they are in fact searching for "Call of Duty".. I need the T-SQL script to look at each individual word in the string. My current script only finds "Call Of Duty". I understand that T-SQL cant do arrays, but there are ways of doing it. Here is my current code that only finds "Call of Duty" if you type it identically.

Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [sbuser].[tf_ProductSearch] (
    @PG int,
    @ROWCT numeric(18,2),
    @COLCT numeric(18,2),
    @StoreItemCatID bigint,
    @SearchStr varchar(100) = null)

RETURNS @OUT TABLE (
    StoreItemID bigint,
    ProductName varchar(50),
    ProductDesc varchar(1000),
    ItemPrice float,
    Active bit,
    CreateDate datetime,
    ShipWeight float,
    TaxExempt bit,
    ShipCost float,
    Keywords varchar(1000),
    PG int,
    MAXPG INT,
    TOTALRECS INT,
    CoverImg varchar(50),
    StoreItemCatID bigint )

AS

BEGIN

        DECLARE @START numeric(18,2)
        DECLARE @END numeric(18,2)
        DECLARE @SIZE numeric(18,2)
        DECLARE @MAXPG numeric(18,2)
        DECLARE @TOTALRECS numeric(18,2)    
        DECLARE @TOTALRECS_INT int
        DECLARE @MAXPG_INT int
        DECLARE @TOTALRECS_REMAINDER numeric(18,2)
        SET @SIZE = @ROWCT * @COLCT
        SET @Start = (((@PG - 1) * @Size) + 1)
        SET @END = (@START + @SIZE - 1)


        DECLARE @TMP1 TABLE (
        TmpID bigint identity(1,1) primary key,
        StoreItemID bigint,
        ProductName varchar(50),
        ProductDesc varchar(1000),
        ItemPrice float,
        Active bit,
        CreateDate datetime,
        ShipWeight float,
        TaxExempt bit,
        ShipCost float,
        Keywords varchar(1000),
        CoverImg varchar(50),
        StoreItemCatID bigint )
        

        IF @StoreItemCatID IS NULL
            BEGIN
                INSERT INTO @TMP1 
                SELECT
                a.StoreItemID,
                a.ProductName,
                a.ProductDesc,
                a.ItemPrice,
                a.Active,
                a.CreateDate,
                a.ShipWeight,
                a.TaxExempt,
                a.ShipCost,
                a.Keywords,
                sbuser.sf_StoreItemCoverImg(a.StoreItemID) AS CoverImg,
                a.StoreItemCatID
                FROM StoreItem a
                WHERE
                a.ProductName LIKE '%' + @SearchStr + '%'
                AND a.Deleted = 0    
                AND a.Active = 1
                ORDER BY a.ProductName 
            END
        ELSE

            BEGIN
                INSERT INTO @TMP1 
                SELECT
                a.StoreItemID,
                a.ProductName,
                a.ProductDesc,
                a.ItemPrice,
                a.Active,
                a.CreateDate,
                a.ShipWeight,
                a.TaxExempt,
                a.ShipCost,
                a.Keywords,
                sbuser.sf_StoreItemCoverImg(a.StoreItemID) AS CoverImg,
                a.StoreItemCatID
                FROM StoreItem a
                WHERE
                a.ProductName LIKE '%' + @SearchStr + '%'
                AND a.StoreItemCatID = @StoreItemCatID
                AND a.Deleted = 0    
                AND a.Active = 1
                OR a.StoreItemCatID IN (SELECT StoreItemCatID FROM StoreItemCat WHERE ParentID = @StoreItemCatID)
                AND a.Deleted = 0    
                AND a.Active = 1
                ORDER BY a.ProductName
            END



        SELECT @TOTALRECS = MAX(TMPID) FROM @TMP1
        SELECT @MAXPG = @TOTALRECS / @SIZE
        SET @TOTALRECS_REMAINDER = @TOTALRECS % @SIZE

        SET @MAXPG_INT = CAST(@MAXPG AS INT)
        SET @TOTALRECS_INT = CAST(@TOTALRECS AS INT)


        IF @TOTALRECS_REMAINDER > 0 
            BEGIN
                SET @MAXPG_INT = @MAXPG_INT + 1
            END        


        
        INSERT INTO @OUT
        SELECT 
            StoreItemID,
            ProductName,
            ProductDesc,
            ItemPrice,
            Active,
            CreateDate,
            ShipWeight,
            TaxExempt,
            ShipCost,
            Keywords,
            @PG,
            @MAXPG_INT,
            @TOTALRECS_INT,
            CoverImg,
            StoreItemCatID
            FROM @TMP1
            WHERE (TmpID >= @Start) AND (TmpID <= @END)        
    
    RETURN

    
END

The @SearchStr is the obvious search string. If anyone can modify this code to search each word in the string so it will find "Call of Duty" if I type "Duty Call" then I will be forever in your debt.

I am using SQL Server 2005

Kind Regards,

Paul

Old Pedant · August 18th, 2010, 05:32 PM

It *can* be done in T-SQL, but building up a string of SQL code and then doing

Code:

    EXEC (@sql)

but it's a pain in the neck.

Hmmm... But maybe not. Are you willing to limit the number of words you are going to search for? Say a max of 5 words??? If so, then we can do it without needing to use EXEC ( )

Old Pedant · August 18th, 2010, 05:36 PM

Oh, and how many words will you insist on matching??? If the user enters "Call of Duty" you say you will accept "Duty Call". But what happened to the "of"??? Okay, we could ignore (say) 1 and 2 letter words. But what about "today in special forces"? Would matching on "special today" or "special forces" or "forces today" be enough??

And do the words have to be adjacent? Would "call of duty" need to find "duty to all callers"???

Text processing is *not* simple. You might be better off investigating SQL Server's Full Text Search capabilities and not trying to do LIKE matches.

neojakey · August 18th, 2010, 05:36 PM

I don't want to really limit the number of words that a user can input into the search query. But if I were to take that route.. how could it be done..?

neojakey · August 18th, 2010, 05:46 PM

I want the product "Call Of Duty" found if the user typed "Dut Call" or "Cal of" or "Of Duty Call", what a typical user might type (these are just examples). It could be any input really. I want to match as many words as possible or sections of words.

Jeff Moden · August 18th, 2010, 06:05 PM

Wouldn't it be better to instantiate Full Text Search for this?

neojakey · August 18th, 2010, 06:06 PM

How do I implement a full text search..?

Old Pedant · August 18th, 2010, 08:05 PM

http://msdn.microsoft.com/en-us/library/ms142571.aspx