时间戳

首页 > 解决方案 > 在 case 语句中搜索字符串是否存在特定字符

sql - 在 case 语句中搜索字符串是否存在特定字符

问题描述

我有以下 SQL，CASE包括D.TIN_TYPE. 我想修改CASE语句（或使用替代方法）以添加到第二个 WHEN 条件，以便字符串中WHEN D.TIN_TYPE = 'S' AND有一个逗号（，），D.PAYEE_NAME1然后使用 LEFT/CHARINDEX、RIGHT/CHARINDEX 函数将名称分成两列，否则只需按原样显示字符串（如果字符串中不包含逗号）。该列的格式不一致，为Lastname, Firstname（有些是Firstname Lastname），因此我需要检查字符串中是否存在逗号。我怎样才能做到这一点？

    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

编辑：

这是编辑后的 ​​SQL，在 CASE 语句中使用了额外的 WHEN。它运行，但对于姓氏列（RIGHT 函数），它同时返回名字和姓氏。我怎样才能返回姓氏（逗号前的字符）？我还尝试对每个 WHEN 使用 END AS 'First Name' 和 END AS 'Last Name' ，但出现语法错误。有没有办法给列赋予不同的名称？

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) > 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) > 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 > 0.00
     AND A.VENDOR_ID <> '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) > 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) > 0 THEN RIGHT(D.PAYEE_NAME1, LEN(PAYEE_CITYSTZIP) - CHARINDEX(',', D.PAYEE_NAME1)+1 ) --END AS 'Last Name'
       ELSE D.PAYEE_NAME1
       END

标签： sqlsql-server-2014

解决方案

您将需要为此创建多个 case 语句。我的建议是创建 3 个案例陈述：一个用于非拆分，一个用于第一个拆分，一个用于第二个拆分。

要检查逗号，您可以使用以下内容：

select 
    split1=case
        WHEN CHARINDEX(',','a,b') > 0 AND D.TIN_TYPE = 'S' THEN left('a,b', CHARINDEX(',','a,b')-1)
    END,
    split2=case
        WHEN CHARINDEX(',','a,b') > 0 AND D.TIN_TYPE = 'S' THEN right('a,b', len('a,b')-CHARINDEX(',','a,b'))
    END  

Results:
split1  split2
a        b

请参见CASE (Transact-SQL) 示例 B

编辑： 尝试从您编辑的查询中提取的较短查询。让它工作，然后开始在 GROUP BY 中添加其余的条件，我认为这就是你的问题所在。

看看我对 PAYEE_FULLNAME、PAYEE_FIRSTNAME、PAYEE_LASTNAME 做了什么。

    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,
PAYEE_FULLNAME=CASE 
        WHEN D.TIN_TYPE = 'F' THEN LEFT(D.PAYEE_NAME1, 30)
        ELSE D.PAYEE_NAME1
     END , 
PAYEE_FIRSTNAME=CASE 
        WHEN D.TIN_TYPE = 'S' AND CHARINDEX(',',D.PAYEE_NAME1) > 0 THEN LEFT(D.PAYEE_NAME1,CHARINDEX(',', D.PAYEE_NAME1)-1)  --END AS 'First Name'
       END ,
PAYEE_LASTNAME=CASE 
        WHEN D.TIN_TYPE = 'S' AND CHARINDEX(',',D.PAYEE_NAME1) > 0 THEN RIGHT(D.PAYEE_NAME1, LEN(PAYEE_CITYSTZIP) - CHARINDEX(',', D.PAYEE_NAME1)+1 ) --END AS 'Last Name'
     END
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 > 0.00
     AND A.VENDOR_ID <> '80577A' )

推荐阅读