Post reply

SQl Query

  • December 1, 2010 at 6:45 am

    #232068

    Hi

    In my table the below multiple values with comma separated

    Asset Protection Lead,Corporate 1

    Asset Protection Lead Post,Corporate 2

    Corporate,Asset Protection Lead 3

    Coporate 4

    Asset Protection Lead 5

    Here i have to retrieve Asset Protection Lead and Corporate.

    So i have used the below query.

    SELECT * FROM table1

    WHERE CONTAINS(field1 ,'"Asset Protection Lead"OR"Corporate"')

    So it should retrieve

    Asset Protection Lead,Corporate 1

    Corporate,Asset Protection Lead 3

    Coporate 4

    Asset Protection Lead 5

    But i am retrieving Asset Protection Lead Post also.

    can u please any help me for this issue

  • December 1, 2010 at 7:33 am

    #1257354

    1. Are the numbers at the end a record identifier, or part of the name?

    2. The row that you don't want to return also contains "corporate". Since you're using an OR, it should be returned.

    3. If you want to get only exact matches, you'll have to split the string up into its parts, and then compare for a match. I highly recommend using the DelimitedSplit8k function for splitting the string up. Click here for the latest Delimited Split Function.

    4. If you have further questions, please review the article that is the first link in my signature. Then post your table structures and sample data as demonstrated in that article, and also provide what your expected results are based upon the sample data you provided. Help us help you.

    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

  • December 2, 2010 at 2:32 am

    #1257760

    Hi

    Thanks for your reply.

    1. for your first question, yes 1,2,3,4,5 is record identifier

    3. i can understand using this DelimitedSplit8k function i can split the values

    like

    Itemnumber Item

    1 Asset Protection Lead

    2 Corporate

    and after that i made a query like

    SElect ClassLevel3 from table1 where (field1 IN

    (SElect Item from dbo.DelimitedSplit8K('Asset Protection Lead,Corporate',','))

    but here i am getting only

    field1 id

    Coporate 4

    Asset Protection Lead 5

    Sometimes i am having the mulitple combination of Corporate,Asset Protection Lead

    like

    field1 id

    Asset Protection Lead,Corporate 1

    Corporate,Asset Protection Lead 3

    Coporate 4

    Asset Protection Lead 5

    Here some cases ,Asset Protection Lead Post,Corporate 2

    It should not come.Exact Pharse only should come Asset Protection Lead combinations.

    How can i do this? Please help me for this.

  • December 2, 2010 at 2:39 am

    #1257763

    Hi

    I got it. below is the query to do that using split functionlity.

    Thanks for your DelimitedSplit8K function.

    select field1 from table1 where field1 like '%Asset Protection Lead,Corporate%'

    or table1 IN(SElect field1 from dbo.DelimitedSplit8K('Asset Protection Lead,Corporate',','))

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

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