SQL使用RIGHT配合CHARINDEX截取字段结果不正确问题排查

问题根源 #

你对RIGHT函数的参数逻辑使用错误：

• RIGHT(字符串, N)的第二个参数N代表从字符串末尾向前截取的字符总数量
• CHARINDEX返回的是分隔符从字符串左侧开始计数的位置编号，两个值完全不匹配，因此会出现固定偏移。

以你举的两个异常结果为例验证：

1. 姓氏PEREZ-MARTINEZ总长度为14，连字符位置为6，你直接传入RIGHT的参数是6，即从末尾取6个字符，得到的就是最后6位RTINEZ，和你遇到的异常完全一致
2. 姓氏PICENO ESPINOZA总长度为15，空格位置为7，传入RIGHT参数为7，取最后7位得到SPINOZA，也符合你遇到的异常情况。

修正方案 #

分隔符后的字符总数 = 整个姓氏的总长度 - 分隔符的位置编号，将RIGHT的第二个参数替换为对应计算值即可。

修正后的查询语句如下：

select      top 1000
            BENE_FIRST_NAME, 
            BENE_LAST_NAME,
            FirstNm =
                case
                    when BENE_FIRST_NAME like '% %' then
                        left(BENE_FIRST_NAME, CHARINDEX(' ', BENE_FIRST_NAME) - 1) -- 加-1可去掉末尾的分隔符，不需要保留可以删掉-1
                    when BENE_FIRST_NAME like '%-%' then
                        left(BENE_FIRST_NAME, CHARINDEX('-', BENE_FIRST_NAME) - 1)
                    else BENE_FIRST_NAME
                end,
            LastNm =
                case
                    when BENE_LAST_NAME like '% %' then
                        right(BENE_LAST_NAME, LEN(BENE_LAST_NAME) - CHARINDEX(' ', BENE_LAST_NAME))
                    when BENE_LAST_NAME like '%-%' then
                        right(BENE_LAST_NAME, LEN(BENE_LAST_NAME) - CHARINDEX('-', BENE_LAST_NAME))
                    else BENE_LAST_NAME
                end,
            CharIndxDash = CHARINDEX('-', BENE_LAST_NAME),
            CharIndxSpace = CHARINDEX(' ', BENE_LAST_NAME)
from        xMIUR_Elig_Raw_v3

内容的提问来源于stack exchange，提问作者WannabeDev