title: Search string inside case statement for existence of specific character
body: <p>I have the following SQL, including a <code>CASE</code> statement for <code>D.TIN_TYPE</code>. I want to modify the <code>CASE</code> statement (or use an alternative) to add onto the 2nd WHEN condition so that <code>WHEN D.TIN_TYPE = 'S' AND</code> there is a comma (,) within the <code>D.PAYEE_NAME1</code> string then break the name into two columns using LEFT/CHARINDEX, RIGHT/CHARINDEX function, Otherwise just display the string as-is (if there is not a comma contained in the string). The column is not consistently formatted as <em>Lastname, Firstname</em> (some are <em>Firstname Lastname</em>) so that is why I need to check for the existence of the comma in the string. How can I accomplish this?</p>
  
  <pre><code>    SELECT D.PAYER_TIN, '001', YEAR( (CONVERT(CHAR(10),A.PYMNT_DT,121))), D.PAYEE_TIN, 
      CASE WHEN  D.TIN_TYPE = 'F' THEN '001' 
           WHEN  D.TIN_TYPE = 'S' THEN '002' 
           END AS 'Taxpayer Identification Type', 
      A.VENDOR_ID,
      CASE WHEN D.TIN_TYPE = 'F' THEN LEFT(D.PAYEE_NAME1, 30)
           WHEN D.TIN_TYPE = 'S' AND --Additional logic for existence of comma--
           ELSE D.PAYEE_NAME1
           END AS 'PAYEE_NAME1', 
      D.PAYEE_NAME1,
      D.PAYEE_NAME2,  
      D.PAYEE_ADDRESS1, 
      LEFT(PAYEE_CITYSTZIP,CHARINDEX(',', PAYEE_CITYSTZIP)-1), 
      SUBSTRING(PAYEE_CITYSTZIP, CHARINDEX(',', PAYEE_CITYSTZIP)+2, 2), 
      RIGHT(PAYEE_CITYSTZIP, LEN(PAYEE_CITYSTZIP) - CHARINDEX(',', PAYEE_CITYSTZIP)-7), 
      'US', 
      D.WTHD_BOX1, 
      D.WTHD_BOX2, 
      D.WTHD_BOX3, 
     FROM PS_WTHD_TRXN_TBL A, PS_TMP_1099_COPYB D, PS_GHS_1099_PA_VW2 E 
     WHERE ( A.WTHD_TYPE = D.WTHD_TYPE 
           AND A.VENDOR_ID = D.VENDOR_ID 
           AND YEAR( A.PYMNT_DT) = '2018')
     GROUP BY  D.PAYER_TIN,  YEAR( (CONVERT(CHAR(10),A.PYMNT_DT,121))),  D.PAYEE_TIN,  CASE WHEN  D.TIN_TYPE = 'F' THEN '001' 
      WHEN  D.TIN_TYPE = 'S' THEN '002' 
      END,  A.VENDOR_ID,  D.PAYEE_NAME1,  D.PAYEE_NAME2,   D.PAYEE_ADDRESS1, 
      LEFT(PAYEE_CITYSTZIP,CHARINDEX(',', PAYEE_CITYSTZIP)-1),  
      SUBSTRING(PAYEE_CITYSTZIP, CHARINDEX(',', PAYEE_CITYSTZIP)+2, 2),  
      RIGHT(PAYEE_CITYSTZIP, LEN(PAYEE_CITYSTZIP) - CHARINDEX(',', PAYEE_CITYSTZIP)-7), 
      D.WTHD_BOX1,  D.WTHD_BOX2,  D.WTHD_BOX3,  D.WTHD_BOX6,  D.WTHD_BOX7,  D.WTHD_BOX14,  D.WTHD_BOX16,  D.WTHD_BOX18
      , CASE WHEN D.TIN_TYPE = 'F' THEN LEFT(D.PAYEE_NAME1, 30)
           ELSE D.PAYEE_NAME1
           END
  </code></pre>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Here is the edited SQL, using an extra WHEN in the CASE statement. It runs but for the last name column (RIGHT function) it's returning both the first and last name. How can I just return the last name (characters before the comma)? Also I tried using END AS 'First Name' and END AS 'Last Name' for each WHEN but I got a syntax error. Is there a way to give the columns distinct names?</p>
  
  <pre><code>SELECT D.PAYER_TIN, '001', 13679089, YEAR( (CONVERT(CHAR(10),A.PYMNT_DT,121))), D.PAYEE_TIN, 
  CASE WHEN  D.TIN_TYPE = 'F' THEN '001' 
       WHEN  D.TIN_TYPE = 'S' THEN '002' 
       END AS 'Taxpayer Identification Type', 
  A.VENDOR_ID,
  CASE WHEN D.TIN_TYPE = 'F' THEN LEFT(D.PAYEE_NAME1, 30)
       WHEN D.TIN_TYPE = 'S' AND CHARINDEX(',',D.PAYEE_NAME1) &gt; 0 THEN LEFT(D.PAYEE_NAME1,CHARINDEX(',', D.PAYEE_NAME1)-1)  --END AS 'First Name'
       WHEN D.TIN_TYPE = 'S' AND CHARINDEX(',',D.PAYEE_NAME1) &gt; 0 THEN RIGHT(D.PAYEE_NAME1, LEN(PAYEE_CITYSTZIP) - CHARINDEX(',', D.PAYEE_NAME1)+1 ) --END AS 'Last Name'
       ELSE D.PAYEE_NAME1
       END AS 'PAYEE_NAME1', 
  D.PAYEE_NAME1,
  D.PAYEE_NAME2,  
  D.PAYEE_ADDRESS1, 
  LEFT(PAYEE_CITYSTZIP,CHARINDEX(',', PAYEE_CITYSTZIP)-1), 
  SUBSTRING(PAYEE_CITYSTZIP, CHARINDEX(',', PAYEE_CITYSTZIP)+2, 2), 
  RIGHT(PAYEE_CITYSTZIP, LEN(PAYEE_CITYSTZIP) - CHARINDEX(',', PAYEE_CITYSTZIP)-7), 
  'US', 
  D.WTHD_BOX1, 
  D.WTHD_BOX2, 
  D.WTHD_BOX3, 
  D.WTHD_BOX6, 
  D.WTHD_BOX7, 
  D.WTHD_BOX14, 
  D.WTHD_BOX16, 
  D.WTHD_BOX18 
  FROM PS_WTHD_TRXN_TBL A, PS_TMP_1099_COPYB D, PS_GHS_1099_PA_VW2 E 
  WHERE ( A.WTHD_TYPE = D.WTHD_TYPE 
       AND A.VENDOR_ID = D.VENDOR_ID 
       AND YEAR( A.PYMNT_DT) = '2018'
       AND A.WTHD_CLASS IN ('01','02','07') 
       AND A.VENDOR_ID = E.VENDOR_ID 
       AND A.VNDR_LOC = E.VNDR_LOC 
       AND E.YEAR = '2018' 
       AND D.WTHD_CNTL_ID = 'TGC' 
       AND D.WTHD_BOX18 &gt; 0.00
       AND A.VENDOR_ID &lt;&gt; '80577A' ) 
  GROUP BY  D.PAYER_TIN,  YEAR( (CONVERT(CHAR(10),A.PYMNT_DT,121))),  D.PAYEE_TIN,  CASE WHEN  D.TIN_TYPE = 'F' THEN '001' 
  WHEN  D.TIN_TYPE = 'S' THEN '002' 
  END,  A.VENDOR_ID,  D.PAYEE_NAME1,  D.PAYEE_NAME2,   D.PAYEE_ADDRESS1, 
  LEFT(PAYEE_CITYSTZIP,CHARINDEX(',', PAYEE_CITYSTZIP)-1),  
  SUBSTRING(PAYEE_CITYSTZIP, CHARINDEX(',', PAYEE_CITYSTZIP)+2, 2),  
  RIGHT(PAYEE_CITYSTZIP, LEN(PAYEE_CITYSTZIP) - CHARINDEX(',', PAYEE_CITYSTZIP)-7), 
  D.WTHD_BOX1,  D.WTHD_BOX2,  D.WTHD_BOX3,  D.WTHD_BOX6,  D.WTHD_BOX7,  D.WTHD_BOX14,  D.WTHD_BOX16,  D.WTHD_BOX18
  , CASE WHEN D.TIN_TYPE = 'F' THEN LEFT(D.PAYEE_NAME1, 30)
       ELSE D.PAYEE_NAME1
       END, 
         CASE WHEN D.TIN_TYPE = 'F' THEN LEFT(D.PAYEE_NAME1, 30)
         WHEN D.TIN_TYPE = 'S' AND CHARINDEX(',',D.PAYEE_NAME1) &gt; 0 THEN LEFT(D.PAYEE_NAME1,CHARINDEX(',', D.PAYEE_NAME1)-1)  --END AS 'First Name'
         WHEN D.TIN_TYPE = 'S' AND CHARINDEX(',',D.PAYEE_NAME1) &gt; 0 THEN RIGHT(D.PAYEE_NAME1, LEN(PAYEE_CITYSTZIP) - CHARINDEX(',', D.PAYEE_NAME1)+1 ) --END AS 'Last Name'
         ELSE D.PAYEE_NAME1
         END
  </code></pre>