SQL charindex throwing Invalid length parameter passed to the LEFT or SUBSTRING function because of period?

Question

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])
    )

The error is originating from CHARINDEX('.', [email])

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!

EDIT.

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.

Answer 1

The error is originating from

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

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.

Try this instead.

CHARINDEX('@',[email] + '@')-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.

Answer 2

I'm not sure this is your only problem. I am guessing that you are trying to look at the first part of an e-mail address and compare it with the first part of the domain. For example if the e-mail address is "[email protected]" then you are looking for "name" and "company". Substring does not take 2 positions, it takes a position and a length. Therefore to get "company" you would need to do this:

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

The +1 and -1 are to account for the fact that CHARINDEX would give you the position of the "@" so it would include the "@" in the result.

Unfortunately this won't always work because if you have an address like "[email protected]" then the position of the first "." will be less than the position of the "@" resulting in a negative number.

Therefore you will need to do this:

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

This would make sure you are looking for the first "." after the "@". However, this still doesn't work if you don't have a "@" but you do have a "." (such as "invalidemail.companay.com"). Therefor you could do the solution above to add a '@' to the end, but a better way would be like this:

SUBSTRING(
    [email],
    CHARINDEX('@', [email]) + 1,
    CASE WHEN 
        CHARINDEX('.', [email], CHARINDEX('@', [email])) - CHARINDEX('@', [email]) - 1 < 0 
    THEN 0 
    ELSE 
        CHARINDEX('.', [email], CHARINDEX('@', [email])) - CHARINDEX('@', [email]) - 1
    END
)