Title search in SQL With replacement of noice words

Question

I have Two tables first us IMG_detail

S.NO    Title
1       women holding stack  of gifts
2       Rear view of a man playing golf
3       Women holding gifts
4       Close-up of a golf ball on a tee
5       Businessman reading a newspaper and smiling

and Second is tbl_NoiceWords

SN  Key
1   and
2   on 
3   of 
4   the
5   view

The second table is Noise table where the noice words contain...Which is replace with the search text ...If my Search text is This is the Man then the Exact search text is This Man.

I want to search text With replace of Noice words from the table NoiceWords. Please suggest me the best way to search text string.

Could you add your expecting result because its difficult to understand what exactly you want? — The Reason, Jun 22 '15 at 10:41
possible duplicate of [How to search multiple strings or chars in a field in sql](http://stackoverflow.com/questions/15541487/how-to-search-multiple-strings-or-chars-in-a-field-in-sql) the accepted answer there appears to do what you are trying to. — Daniel E., Jun 22 '15 at 10:45
@The if serch text is ` women holding stack of gifts` then result should be `S.NO` 1 — Gitz, Jun 22 '15 at 10:54
Can you explain how the 2nd table come into the 'search' text ? — S.Krishna, Jun 22 '15 at 10:59
@S.Krishna Sir The second table is Noise table where the noice words contain...Which is replace with the search text ...If my Search text is `This is the Man` then the Exact search text is `This Man`.. — Gitz, Jun 22 '15 at 11:02
please post the sample out put as you have posted for tables so we can have better idea @Gitz — mohan111, Jun 22 '15 at 11:16

Giorgi Nakeuri · Accepted Answer · 2015-06-22T11:53:49.873

I think you want something like this:

DECLARE @nw TABLE ( sn INT, [key] VARCHAR(100) )

INSERT  INTO @nw
VALUES  ( 1, 'and' ),
        ( 2, 'on' ),
        ( 3, 'of' ),
        ( 4, 'the' ),
        ( 5, 'view' )


DECLARE @s VARCHAR(100) = 'view This of is the Man';
WITH    cte
          AS ( SELECT   sn ,
                        REPLACE(@s, [key], '') AS s
               FROM     @nw
               WHERE    sn = 1
               UNION ALL
               SELECT   n.sn ,
                        REPLACE(s, n.[key], '') AS s
               FROM     @nw n
                        JOIN cte c ON c.sn + 1 = n.sn
             )
    SELECT TOP 1 @s =
            REPLACE(REPLACE(REPLACE(s, ' ', '[]'), '][', ''), '[]', ' ')
    FROM    cte
    ORDER BY sn DESC

Output:

This is Man

First you recursively removing noise words from search string, and in the end a little trick to remove duplicate continuous spaces.

Then you can filter base table like:

SELECT * FROM TableName WHERE Title LIKE '%' + @s + '%'

May be you want to consider FULL TEXT SEARCH? I suspect you also want to remove those noise words from base table while searching. It will be very slow. Full Text Search is optimized for such type of work. It includes noise words, stoplists and more...

If you don't want to use Full Text Search, you can add additional column to your base table, which will hold the value from Title but without noise words and search based on that column.

But if you insist here is the full code for this:

DECLARE @t TABLE
    (
      SNo INT ,
      Title VARCHAR(100)
    )
INSERT  INTO @t
        ( SNo, Title )
VALUES  ( 1, 'women holding stack  of gifts' ),
        ( 2, 'Rear view of a man playing golf' ),
        ( 3, 'Women holding gifts' ),
        ( 4, 'Women holding gifts' ),
        ( 5, 'Businessman reading a newspaper and smiling' ),
        ( 6, 'Hey This some what of is the Man from Chicago' )

DECLARE @nw TABLE
    (
      sn INT ,
      [key] VARCHAR(100)
    )

INSERT  INTO @nw
VALUES  ( 1, 'and' ),
        ( 2, 'on' ),
        ( 3, 'of' ),
        ( 4, 'the' ),
        ( 5, 'view' ),
        ( 6, 'some' ),
        ( 7, 'what' )

And the code:

DECLARE @s VARCHAR(100) = 'view This of is the Man';
WITH    cte
          AS ( SELECT   sn ,
                        REPLACE(@s, [key], '') AS s
               FROM     @nw
               WHERE    sn = 1
               UNION ALL
               SELECT   n.sn ,
                        REPLACE(s, n.[key], '') AS s
               FROM     @nw n
                        JOIN cte c ON c.sn + 1 = n.sn
             )
    SELECT TOP 1
            @s = REPLACE(REPLACE(REPLACE(s, ' ', '[]'), '][', ''), '[]', ' ')
    FROM    cte
    ORDER BY sn DESC

;WITH    cte
          AS ( SELECT   t.* ,
                        n.sn ,
                        REPLACE(t.Title, n.[key], '') AS s
               FROM     @t t
                        JOIN @nw n ON sn = 1
               UNION ALL
               SELECT   c.SNo ,
                        c.Title ,
                        n.sn ,
                        REPLACE(c.s, n.[key], '')
               FROM     cte c
                        JOIN @nw n ON n.sn = c.sn + 1
             )
    SELECT  *
    FROM    cte
    WHERE   REPLACE(REPLACE(REPLACE(s, ' ', '[]'), '][', ''), '[]', ' ') LIKE '%' + @s + '%'

And the output:

SNo Title                                           sn  s
6   Hey This some what of is the Man from Chicago   7   Hey This    is  Man from Chicago

exactly i am doing the same this .. i am replacing the string with C# code then create the same logic as you mention above `SELECT * FROM TableName WHERE TiTle LIKE '%' + @s + '%' ` but did't get the proper result — Gitz, Jun 22 '15 at 11:22
Then it is time to learn! It is not hard. You add `full text index` on `title` column and then filter rows with `CONTAINS()`... — Giorgi Nakeuri, Jun 22 '15 at 11:26
Okay Thanks for your answerer I'll trying to config your code into my DB ..will let you know after completed :) @Giorgi Nakeuri — Gitz, Jun 22 '15 at 11:54

Title search in SQL With replacement of noice words

1 Answers1