how to use more than one string in like expression in sql

Question

I have a list of 20 (or more) words (string) and want to select the rows that have these word in 3 of their column. I should use like expression of sql. but I don't know how to use more than one string in like expression. (I do it with union now, but I have at least 60 select statement and think it reduced the performance, Is it really reduce the performance?)

        //get the advertise that have similar keywords
        foreach (string str in keywords)
        {
            if (str != "")
            {
                if (!string.IsNullOrEmpty(sqlQuery)) sqlQuery += " union";
                sqlQuery = "select * from AD_Advertise where (AdKeyWords like N'%" + str + "%'"
                    + " OR AdTitle like N'%" + str + "%' "
                    + " OR AdDescription like N'%" + str + "%' "
                    + " OR AdGroupItemCode=" + adinfo.AdGroupItemCode + ")"
                    + " AND AdSiteID=" + CMSContext.CurrentSiteID
                    + " AND AdShow='True' "
                    + " AND ItemID != " + ADId;
            }
        }

        ds = cn.ExecuteQuery(sqlQuery,null);//("AD.Advertise.selectall", null, where, "ItemModifiedWhen");

Answer:

At last I used below code:

 if object_id('tempdb..#WordList') is not null
    drop table #WordList
 CREATE TABLE #WordList ( KeyWord nvarchar(100))
 insert into #WordList values (N'حقوقی'),(N'وکیل');

 SELECT DISTINCT *
 FROM AD_ADvertise a
     LEFT JOIN #WordList k 
     ON a.AdKeywords LIKE '%' + k.KeyWord + '%' 
     OR  a.AdTitle LIKE '%' + k.KeyWord + '%' 
     OR a.AdDescription LIKE '%' + k.KeyWord + '%'
 WHERE
    (k.KeyWord IS NOT NULL OR a.AdGroupItemCode = @AdGroupItemCode)
    AND a.AdSiteId = @AdSiteId
    AND a.AdShow = 'True'
    AND a.ItemId != @ItemId
;drop table #WordList

Rather than building the Union query, why don't you create only "OR" statement in foreach loop and than build a final query later. — Rohit Vyas
– Rohit Vyas, Commented Oct 22, 2013 at 13:43
@Rohit About 60 OR statement in where clause don't reduce the speed? — mjyazdani
– mjyazdani, Commented Oct 22, 2013 at 15:56

James S · Accepted Answer · 2013-10-22 14:25:53Z

Create a stored procedure with a table valued parameter that takes your list of strings.

Have a join between the table valued parameter and your tables AD_Advertise on the like.

Heres how to do the Table type + the stored procedure:

CREATE TYPE WordList AS TABLE (Word NVARCHAR(50));
GO

CREATE PROCEDURE GetAddsMatchingKeywords
    @KeywordList WordList READONLY,
    @AdGroupItemCode VARCHAR(50),
    @AdSiteId INT,
    @ItemId INT
AS
    SELECT DISTINCT
        a.AdTitle, 
        a.ItemId -- extend to the full column list
    FROM AD_ADvertise a
        LEFT JOIN @KeywordList k ON a.AdKeywords LIKE '%' + k.Word + '%' OR  a.AdTitle LIKE '%' + k.Word + '%' OR a.AdDescription LIKE '%' + k.Word + '%'
    WHERE
        (k.Word IS NOT NULL OR a.AdGroupItemCode = @AdGroupItemCode)
        AND a.AdSiteId = @AdSiteId
        AND a.AdShow = 'True'
        AND a.ItemId = @ItemId

GO

edit - misread the original question - thought you wanted to match 3 or more words. This version matches any that have a single word in any of the 3 columns - like you wanted I think.

podiluska · Accepted Answer · 2013-10-22 13:40:15Z

1

Don't ever build up a SQL string like that. Read about SQL injection before you go any further.
Ideally, you would have a table of keywords, rather than a string, and a join table to link between the item and the keywords.
If you persist in doing it this way, have a look at Full Text Search

answered Oct 22, 2013 at 13:40

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Comments

dang · Accepted Answer · 2013-10-22 13:40:04Z

0

    //get the advertise that have similar keywords
    foreach (string str in keywords)
    {
        if (str != "")
        {
            if (!string.IsNullOrEmpty(sqlQuery)) sqlQuery += " union";
            sqlQuery = "select * from AD_Advertise where (AdKeyWords + AdTitle + AdDescription like N'%" + str + "%'"
                + " OR AdGroupItemCode=" + adinfo.AdGroupItemCode + ")"
                + " AND AdSiteID=" + CMSContext.CurrentSiteID
                + " AND AdShow='True' "
                + " AND ItemID != " + ADId;
        }
    }

    ds = cn.ExecuteQuery(sqlQuery,null);//("AD.Advertise.selectall", null, where, "ItemModifiedWhen");

answered Oct 22, 2013 at 13:40

dang

1931 gold badge3 silver badges19 bronze badges

Collectives™ on Stack Overflow

how to use more than one string in like expression in sql

Answer:

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Answer:

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related