Post reply

Explain a SELECT statement (Patindex) inside this Email function

  • October 14, 2010 at 3:02 am

    #97519

    Hi All,

    Can anyone pls explain to me step-by-step what the little SELECT part of this function is doing (in English...lol).

    I know the function is checking if an email address is valid or not by returning 1 or 0. But how does it do that validation?

    Many thanks

    Create FUNCTION [dbo].[ValidEmail]

    (

    @vEmail varchar(150)

    )

    RETURNS int

    AS

    BEGIN

    -- Declare the return variable here

    DECLARE @iValid int

    SELECT @iValid = case when len(ltrim(rtrim(@vEmail)))>4

    and patindex('%@%',@vEmail)>1

    then 1 else 0 end

    -- Return the result of the function

    RETURN @iValid

    END

  • October 14, 2010 at 3:47 am

    #1236707

    The query returns 1 when the input value is longer than 4 char (len(ltrim(rtrim(@vEmail)))>4) and contains the "@" char (patindex('%@%',@vEmail)>1).

    PATINDEX returns the offset in the input string where the search string is found. The search string can be a search pattern containing escape characters.

    In this case, "%@%" means "any occurrence of any character, followed by the @ character, followed by any occurrence of any character".

    Hope this is clear enough.

    For reference, see PATINDEX on BOL:

    http://msdn.microsoft.com/en-us/library/ms188395.aspx

    -- Gianluca Sartori

  • October 14, 2010 at 4:22 am

    #1236717

    Very clear indeed. It suddenly became very obvious! Many thanks Gianluca.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply