Post reply

Search Logic Fails

  • May 13, 2014 at 6:20 pm

    #291325

    Hi,

    Below is my table structure. please assume that below CTE as real tables.

    with Market as (

    select 1 as id,'Dominos' as Store union all

    select 2 as id,'WalMart' as Store union all

    select 3 as id,'PizzaHut' as Store union all

    select 4 as id,'Papachinos' as Store union all

    select 5 as id,'Bj''s' as Store union all

    select 6 as id,'WalGreens' as Store union all

    select 7 as id,'DomaicanTree' as Store union all

    select 8 as id,'Whole Foods' as Store

    ),

    Keywords as (select 'Dom' as Keyword union all select'Wal' as Keyword union all select'Bj' as Keyword union all

    select 'WholeFoods' as Keyword)

    SELECT M.id,M.Store

    FROM Market M inner JOIN

    Keywords K ON M.Store LIKE '%'+ K.Keyword + '%'

    order by M.Store

    Expected Result:

    idStore

    5Bj's

    7DomaicanTree

    1Dominos

    6WalGreens

    2WalMart

    8Whole Foods

    Though am using %keyword%, the wholdfoods related record i am unable to fetch. Any suggestion please

  • May 13, 2014 at 6:40 pm

    #1713289

    Makes sense...

    select 'WholeFoods' as Keyword

    will never match 'Whole Foods'. If you fix that, your problem is solved.

  • May 13, 2014 at 7:12 pm

    #1713291

    Thanks for the reply,

    Yes i do agree. Is there any way to achieve these kind of search functionality? Any suggestion please

  • May 13, 2014 at 7:30 pm

    #1713293

    Can you not change the spelling in your list? Otherwise, you would have to use a function to remove the spaces in your store names, and then match on that.

    As I said already, this is the crux of your problem:

    'WholeF' != 'Whole F' (the two do not match. No two ways about it!)

    so if you're trying to match that way, it will fail. The easiest way to fix it would be to fix the spelling in your table of stores.

    UPDATE StoresTable

    SET StoreName = 'Whole Foods'

    WHERE StoreName = 'WholeFoods';

    (or vice versa).

    Go talk to whoever has update rights on the table... have that person fix it. While you can join two tables on LIKE, I wouldn't recommend it. Performance is going to make you cry.

  • May 13, 2014 at 7:39 pm

    #1713294

    Hi piet,

    I agree. i already tried with function and it kills the time. thank you for your suggestion. Appreciate your time.

  • May 13, 2014 at 7:44 pm

    #1713295

    Good luck! Sorry I couldn't be of more help.

    Pieter

  • May 14, 2014 at 8:25 am

    #1713494

    In the Stores table, use a function to compute a persisted column that is the store name with all non-alphabetic characters stripped. Do the comparisons on that column, but return the actual store name in the query.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply