Post reply

How compare a list to a table

  • February 27, 2013 at 8:54 am

    #290284

    Hi,

    I have a list of colors like this {White,Black,Green} and I have a table with this structure [Name];[Color];[IDX]. The data inside my table is :

    item1;Black;1

    item2;Green;2

    item3;Yellow;3

    item4;White;4

    ........

    I wonder if it is possible to write a "SELECT" command to compare each row to the list and if the color is not included in the list, return a word like "Other". so the returned row from above will be like this :

    item1;Black;1

    item2;Green;2

    item3;Other;3

    item4;White;4

    ........

    Currently, I fetch through table and compare each row to the list items and if the color is not in the list update the filed and replace it with "Other" however this procedure takes long and as my table record number is increasing, I need to find another way.

    Any help will be appreciated.

    Thanks

  • February 27, 2013 at 9:08 am

    #1591600

    So, using this sample data: -

    IF object_id('tempdb..#testEnvironment') IS NOT NULL

    BEGIN

    DROP TABLE #testEnvironment;

    END;

    SELECT Name, Color, IDX

    INTO #testEnvironment

    FROM (VALUES('item1','Black',1),

    ('item2','Green',2),

    ('item3','Yellow',3),

    ('item4','White',4)

    )a(Name, Color, IDX);

    I'd do something like this: -

    DECLARE @string VARCHAR(8000) = '{White,Black,Green}';

    SET @string = REPLACE(REPLACE(@string,'{',''),'}','');

    SELECT Name, ISNULL(Item,'Other') AS Color, IDX

    FROM #testEnvironment

    OUTER APPLY (SELECT Item

    FROM [dbo].[DelimitedSplit8K](@string,',')

    WHERE Item = Color) b;

    Which returns: -

    Name Color IDX

    ----- ------ -----

    item1 Black 1

    item2 Green 2

    item3 Other 3

    item4 White 4

    You can find the code for the DelimitedSplit8K here --> http://qa.sqlservercentral.com/articles/Tally+Table/72993/%5B/url%5D

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • February 27, 2013 at 3:55 pm

    #1591786

    Dear SSCrazy,

    Your solution saved me. It works perfectly and I learned about "OUTER APPLY" which I was not aware of.

    Thanks again.

  • February 27, 2013 at 6:14 pm

    #1591804

    There is no need for outer apply here

    DECLARE @string VARCHAR(8000) = '{White,Black,Green}';

    SET @string = REPLACE(REPLACE(@string,'{',''),'}','');

    SELECT Name, ISNULL(Item,'Other') AS Color, IDX

    FROM #testEnvironment a

    LEFT JOIN [dbo].[DelimitedSplit8K](@string,',') b on b.Item = a.Color ;

    _____________
    Code for TallyGenerator

  • February 27, 2013 at 6:30 pm

    #1591809

    Dear SSCarpal Tunnel,

    Thanks for the help. I will check which option give me better performance.

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

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