Like operator with additional conditions

• sql_2005_fan

  SSCommitted

  Points: 1871

  More actions

  March 22, 2019 at 10:21 am

  #409713

  Hi,
  I have the following values in a column.

  TT,FF,RR,GG,MM,NN
  LL,PP,TT,NN
  RR,NN,MM,LL

  If the value had RR ,MM and NN  then then I need to return 'Yes'
    When value has RR and no MM and NN then I need to return 'No'
  When the value has no RR then I should resturn 'NA'

  How can I do this?
  Thanks,.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 22, 2019 at 10:51 am

  #2025233

  sql_2005_fan - Friday, March 22, 2019 10:21 AM

  Hi,
  I have the following values in a column.

  TT,FF,RR,GG,MM,NN
  LL,PP,TT,NN
  RR,NN,MM,LL

  If the value had RR ,MM and NN  then then I need to return 'Yes'
    When value has RR and no MM and NN then I need to return 'No'
  When the value has no RR then I should resturn 'NA'

  How can I do this?
  Thanks,.

  You mean something like this?
  

  IF OBJECT_ID('tempdb..#TestData') IS NOT NULL
DROP TABLE [#TestData];

  CREATE TABLE [#TestData] ([OnlyColumnProvided] varchar(32));
INSERT INTO [#TestData]
(
[OnlyColumnProvided]
)
VALUES
(
'TT,FF,RR,GG,MM,NN' -- OnlyColumnProvided - varchar(32)
)
, (
  'LL,PP,TT,NN'
)
, (
  'RR,NN,MM,LL'
), (
  'RR,ND,MD,LL'
);

  SELECT

  .[OnlyColumnProvided]
, [Results] = CASE WHEN

  .[OnlyColumnProvided] LIKE '%RR%' AND

  .[OnlyColumnProvided] LIKE '%MM%' AND

  .[OnlyColumnProvided] LIKE '%NN%' THEN 'Yes'
       WHEN

  .[OnlyColumnProvided] LIKE '%RR%' AND

  .[OnlyColumnProvided] NOT LIKE '%MM%' AND

  .[OnlyColumnProvided] NOT LIKE '%NN%' THEN 'No'
       WHEN

  .[OnlyColumnProvided] NOT LIKE '%RR%' THEN 'N/A'
      END
FROM
[#TestData] AS

  ;


• Scott Coleman

  One Orange Chip

  Points: 27452

  More actions

  March 22, 2019 at 12:52 pm

  #2025270

  It is possible to do it while only evaluating each condition once.
  SELECT Results = CASE
            WHEN td.OnlyColumnProvided NOT LIKE '%RR%' THEN 'N/A'
            WHEN td.OnlyColumnProvided LIKE '%MM%' AND td.OnlyColumnProvided LIKE '%NN%' THEN 'Yes'
            ELSE 'NO' END


• drew.allen

  SSC Guru

  Points: 76985

  More actions

  March 22, 2019 at 12:56 pm

  #2025273

  Lynn Pettis - Friday, March 22, 2019 10:51 AM

  sql_2005_fan - Friday, March 22, 2019 10:21 AM

  Hi,
  I have the following values in a column.

  TT,FF,RR,GG,MM,NN
  LL,PP,TT,NN
  RR,NN,MM,LL

  If the value had RR ,MM and NN  then then I need to return 'Yes'
    When value has RR and no MM and NN then I need to return 'No'
  When the value has no RR then I should resturn 'NA'

  How can I do this?
  Thanks,.

  You mean something like this?
  

  IF OBJECT_ID('tempdb..#TestData') IS NOT NULL
DROP TABLE [#TestData];

  CREATE TABLE [#TestData] ([OnlyColumnProvided] varchar(32));
INSERT INTO [#TestData]
(
[OnlyColumnProvided]
)
VALUES
(
'TT,FF,RR,GG,MM,NN' -- OnlyColumnProvided - varchar(32)
)
, (
  'LL,PP,TT,NN'
)
, (
  'RR,NN,MM,LL'
), (
  'RR,ND,MD,LL'
);

  SELECT

  .[OnlyColumnProvided]
, [Results] = CASE WHEN

  .[OnlyColumnProvided] LIKE '%RR%' AND

  .[OnlyColumnProvided] LIKE '%MM%' AND

  .[OnlyColumnProvided] LIKE '%NN%' THEN 'Yes'
       WHEN

  .[OnlyColumnProvided] LIKE '%RR%' AND

  .[OnlyColumnProvided] NOT LIKE '%MM%' AND

  .[OnlyColumnProvided] NOT LIKE '%NN%' THEN 'No'
       WHEN

  .[OnlyColumnProvided] NOT LIKE '%RR%' THEN 'N/A'
      END
FROM
[#TestData] AS

  ;


  You can greatly simplify the tests by understanding exactly how CASE works and reordering your tests to take advantage of that.  CASE will stop evaluating once it gets to a condition that evaluates to TRUE, so if it proceeds past a certain condition, we know that it must evaluate to FALSE, and we don't need to test for it.

  
SELECT

  .[OnlyColumnProvided]
, [Results] = CASE
   WHEN

  .[OnlyColumnProvided] NOT LIKE '%RR%' THEN 'N/A'
--  Since we've gotten to this step, we know that td.OnlyColumnProvided NOT LIKE '%RR%' is FALSE,
-- which means that td.OnlyColumnProvided LIKE '%RR%' is TRUE.
-- We don't need to test this condition again. 
     WHEN

  .[OnlyColumnProvided] LIKE '%MM%' AND

  .[OnlyColumnProvided] LIKE '%NN%' THEN 'Yes'
   ELSE 'No'
  END
FROM
[#TestData] AS

  ;


  You also don't say what you want to do when the string contains RR and only one of MM and NN.  I'm assuming that these should also be No, otherwise you'll need different tests.

  Drew

  PS: This is bad database design.  It violates 1st Normal Form, because it has a single field with multiple values.  This makes it hard to accurately query those fields.  Specifically, the solution given may give you incorrect results if it contains something like TT,FF,MRR,MM.

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 22, 2019 at 1:10 pm

  #2025276

  Scott Coleman - Friday, March 22, 2019 12:52 PM

  It is possible to do it while only evaluating each condition once.
  SELECT Results = CASE
            WHEN td.OnlyColumnProvided NOT LIKE '%RR%' THEN 'N/A'
            WHEN td.OnlyColumnProvided LIKE '%MM%' AND td.OnlyColumnProvided LIKE '%NN%' THEN 'Yes'
            ELSE 'NO' END


  I know, just wanted to be sure he saw the appropriate testing.based on what he had posted.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 22, 2019 at 1:11 pm

  #2025277

  drew.allen - Friday, March 22, 2019 12:56 PM

  Lynn Pettis - Friday, March 22, 2019 10:51 AM

  sql_2005_fan - Friday, March 22, 2019 10:21 AM

  Hi,
  I have the following values in a column.

  TT,FF,RR,GG,MM,NN
  LL,PP,TT,NN
  RR,NN,MM,LL

  If the value had RR ,MM and NN  then then I need to return 'Yes'
    When value has RR and no MM and NN then I need to return 'No'
  When the value has no RR then I should resturn 'NA'

  How can I do this?
  Thanks,.

  You mean something like this?
  

  IF OBJECT_ID('tempdb..#TestData') IS NOT NULL
DROP TABLE [#TestData];

  CREATE TABLE [#TestData] ([OnlyColumnProvided] varchar(32));
INSERT INTO [#TestData]
(
[OnlyColumnProvided]
)
VALUES
(
'TT,FF,RR,GG,MM,NN' -- OnlyColumnProvided - varchar(32)
)
, (
  'LL,PP,TT,NN'
)
, (
  'RR,NN,MM,LL'
), (
  'RR,ND,MD,LL'
);

  SELECT

  .[OnlyColumnProvided]
, [Results] = CASE WHEN

  .[OnlyColumnProvided] LIKE '%RR%' AND

  .[OnlyColumnProvided] LIKE '%MM%' AND

  .[OnlyColumnProvided] LIKE '%NN%' THEN 'Yes'
       WHEN

  .[OnlyColumnProvided] LIKE '%RR%' AND

  .[OnlyColumnProvided] NOT LIKE '%MM%' AND

  .[OnlyColumnProvided] NOT LIKE '%NN%' THEN 'No'
       WHEN

  .[OnlyColumnProvided] NOT LIKE '%RR%' THEN 'N/A'
      END
FROM
[#TestData] AS

  ;


  You can greatly simplify the tests by understanding exactly how CASE works and reordering your tests to take advantage of that.  CASE will stop evaluating once it gets to a condition that evaluates to TRUE, so if it proceeds past a certain condition, we know that it must evaluate to FALSE, and we don't need to test for it.

  
SELECT

  .[OnlyColumnProvided]
, [Results] = CASE
   WHEN

  .[OnlyColumnProvided] NOT LIKE '%RR%' THEN 'N/A'
--  Since we've gotten to this step, we know that td.OnlyColumnProvided NOT LIKE '%RR%' is FALSE,
-- which means that td.OnlyColumnProvided LIKE '%RR%' is TRUE.
-- We don't need to test this condition again. 
     WHEN

  .[OnlyColumnProvided] LIKE '%MM%' AND

  .[OnlyColumnProvided] LIKE '%NN%' THEN 'Yes'
   ELSE 'No'
  END
FROM
[#TestData] AS

  ;


  You also don't say what you want to do when the string contains RR and only one of MM and NN.  I'm assuming that these should also be No, otherwise you'll need different tests.

  Drew

  PS: This is bad database design.  It violates 1st Normal Form, because it has a single field with multiple values.  This makes it hard to accurately query those fields.  Specifically, the solution given may give you incorrect results if it contains something like TT,FF,MRR,MM.

  As mentioned above, it was for illustration.  I also agree the design is bad.