Post reply

how to write a query which must identify a particular word from one table and another table

  • February 5, 2010 at 8:26 am

    #386256

    Hi,

    i have two tables like

    table 1:

    Itemno | Name |

    123 WASH Detergent

    table 2:

    itemno | Decription|

    Can you tell me how to write a query where it must choose a particular word in the table1 and bring the all rows from second table2 exactly from the description column the word name which is specified.

    for example when i give '%ASH%' in the query it is giving me 'WASH Detergent'

    its not giving me exact word which i want as it is giving me duplicate values like that.

    so can u help me how to get an exact word without getting duplicate values.

    Thanks,

    S

  • February 5, 2010 at 9:12 am

    #1114535

    i think this is what you are after;

    the "description" field must be like the NAME, and the NAME must be like your search parameter:

    so the "description" must be like '%WASH Detergent%' becuase it was foudn witht he"%ASH%' search parameter:

    select * from Table2

    inner join Table 1 on TABLE2.Description LIKE '%' + TABLE1.NAME +'%'

    WHERE TABLE1.NAME LIKE '%ASH%'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 5, 2010 at 9:43 am

    #1114570

    If you want the word, then try putting a space before/after the wildcards.

    To handle the situation when this word would be at the beginning/ending of the column, add a beginning/ending space on the column.

    i.e. (to borrow from Lowell)

    select * from Table2

    inner join Table 1 on TABLE2.Description LIKE '%' + TABLE1.NAME +'%'

    WHERE ' ' + TABLE1.NAME + ' ' LIKE ' %ASH% '

    By having a wildcard at the beginning of the like expression, you already aren't using an index, so adding the spaces around the field won't hurt performance any worse than what is already being done.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 3 posts - 1 through 3 (of 3 total)

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