Wrox Programmer Forums
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 18th, 2010, 05:23 PM
Registered User
 
Join Date: Aug 2008
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default T-SQL Array Search

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 August 18th, 2010, 05:32 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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 August 18th, 2010, 05:36 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
 
Old August 18th, 2010, 05:36 PM
Registered User
 
Join Date: Aug 2008
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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..?
 
Old August 18th, 2010, 05:46 PM
Registered User
 
Join Date: Aug 2008
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old August 18th, 2010, 06:05 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default Full Text Search???

Wouldn't it be better to instantiate Full Text Search for this?
__________________
--Jeff Moden
 
Old August 18th, 2010, 06:06 PM
Registered User
 
Join Date: Aug 2008
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

How do I implement a full text search..?
 
Old August 18th, 2010, 08:05 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
T-SQL Array Search neojakey SQL Language 0 August 18th, 2010 05:17 PM
Looping through Array Values in T-SQL trufla SQL Server ASP 4 April 13th, 2007 06:14 AM
Multi-Column Array Search RollingWoodFarm Excel VBA 5 August 1st, 2006 06:45 PM
search a file for every element in array and appen sriramus Perl 1 March 6th, 2006 05:36 PM
SQL search SoC Classic ASP Databases 1 August 20th, 2004 08:52 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.