传递给具有CHARDINDEX的RIGHT函数的长度参数无效

Question

我在以下SQL中收到错误"传递给RIGHT函数的长度参数无效"。据我所见，这与CHARDINDEX函数有关。这是在SQL Server 2014上。如何解决这个问题？

SELECT D.PAYER_TIN, '001', 13679089, YEAR( (CONVERT(CHAR(20),A.PYMNT_DT,121))), 
D.PAYEE_TIN, 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_BOX7, D.WTHD_BOX18, 0.00 
FROM PS_WTHD_TRXN_TBL A, PS_TMP_1099_COPYB D, PS_TMP_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 = 'WEST1' 
AND D.WTHD_BOX18 > 0.00) 
GROUP BY  D.PAYER_TIN,  YEAR( (CONVERT(CHAR(20),A.PYMNT_DT,121))),  D.PAYEE_TIN,  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_BOX7,  D.WTHD_BOX18

编辑：

添加了以下case语句(到SELECT和GROUP BY)，它似乎可以处理该条件。事实证明，有一行中，该列的值只有一个逗号("，")(错误的用户/数据输入)，因此从1中减去7将导致负数，从而导致错误。

CASE WHEN PAYEE_CITYSTZIP <> ',' THEN
RIGHT(PAYEE_CITYSTZIP, LEN(PAYEE_CITYSTZIP) - CHARINDEX(',', PAYEE_CITYSTZIP)-7)
ELSE RIGHT(PAYEE_CITYSTZIP, LEN(PAYEE_CITYSTZIP) - CHARINDEX(',', PAYEE_CITYSTZIP)+1) END AS ZIP

Answer 1

对于RIGHT的第二个参数，来自：
https://learn.microsoft.com/en-us/sql/t-sql/functions/right-transact-sql?view=sql-服务器-2017

如果integer_expression为负数，则返回错误。

您得到的错误可能意味着：

LEN(PAYEE_CITYSTZIP) - CHARINDEX(',', PAYEE_CITYSTZIP)-7

返回一个负值