How compare a list to a table

• RZ52

  Mr or Mrs. 500

  Points: 536

  More actions

  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

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  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://www.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/

  ---

• RZ52

  Mr or Mrs. 500

  Points: 536

  More actions

  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.

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  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

• RZ52

  Mr or Mrs. 500

  Points: 536

  More actions

  February 27, 2013 at 6:30 pm

  #1591809

  Dear SSCarpal Tunnel,

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