SQL charindex 抛出无效的长度参数由于句点而传递给 LEFT 或 SUBSTRING 函数? [英] SQL charindex throwing Invalid length parameter passed to the LEFT or SUBSTRING function because of period?

问题描述

我在查询的 where 子句中有以下几行，但我不断收到此错误:

I've got the lines below in the where clause of my query, but I keep getting this error:

Msg 537, Level 16, State 3, Line 3
Invalid length parameter passed to the LEFT or SUBSTRING function.

    SUBSTRING(
        [email], 
        1, 
        CHARINDEX('@',[email])-1
    ) =
    SUBSTRING(
        [email], 
        CHARINDEX('@',[email])+1,
        CHARINDEX('.', [email])
    )

错误源自 CHARINDEX('.', [email])

如果我将句点更改为字母，则不会出现错误.每条记录都有一个句点，即使没有，charindex 函数也会返回 0，这不会导致抛出此错误.我一定错过了一些简单的东西.请帮忙！

If I change the period to a letter, I don't get the error. Every record has a period in it, and even if one didn't, the charindex function would return 0, which wouldn't cause this error to throw. I must be missing something simple. Please help!

编辑.

我试着把它扔进一个 isnull, isnull(CHARINDEX('.', [email]), 1) 以防万一它由于某种原因返回 null，但这没有用

I tried throwing it inside an isnull, isnull(CHARINDEX('.', [email]), 1) just in case it was returning null for some reason, but that didn't work either.

推荐答案

错误源自

CHARINDEX('@',[email])-1

如果数据中没有@符号，charindex返回0，减去1得到-1，在substring函数中是无效的.

If there is no @ symbol in the data, charindex returns 0. You subtract one from that to get -1, which is invalid in the substring function.

试试这个.

CHARINDEX('@',[email] + '@')-1

这会强制匹配，确保 CharIndex 始终返回一个 >= 1 的值，这将导致您的子字符串函数成功.

This forces there to be a match, making sure CharIndex will always return a value >= 1, which will cause your substring function to succeed.

这篇关于SQL charindex 抛出无效的长度参数由于句点而传递给 LEFT 或 SUBSTRING 函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！