Internet

These functions are for working with Internet related data.

fn_create_names_from_email

This function attemtps to create a Full Name from an email address.

CREATE FUNCTION [DBO].[fn_create_names_from_email](
                                                       @text VARCHAR( MAX ) )
RETURNS VARCHAR( MAX ) AS
BEGIN
    DECLARE @Result VARCHAR( MAX );
    DECLARE @text_corrected VARCHAR( MAX );
    DECLARE @first_name VARCHAR( MAX );
    DECLARE @last_name VARCHAR( MAX );
    SELECT @text_corrected = REPLACE( REPLACE( REPLACE( REPLACE( @text, '(', ' (' ), '  ', ' ' ), '/', ' ' ), '_', '.' );

    SELECT @first_name =
        CASE
            WHEN LEN( @text_corrected ) - LEN( REPLACE( @text_corrected, '@', '' ) ) > 0
            THEN
                CASE
                    WHEN LEN( dbo.fn_GetStringPartByDelimiter( @text_corrected, '@', 1 ) ) - LEN( REPLACE( dbo.fn_GetStringPartByDelimiter( @text_corrected, '@', 1 ), '.', '' ) ) = 0
                    THEN dbo.fn_GetStringPartByDelimiter( @text_corrected, '@', 1 )
                    ELSE dbo.fn_ProperCase( dbo.fn_GetStringPartByDelimiter( dbo.fn_GetStringPartByDelimiter( @text_corrected, '@', 1 ), '.', 1 ) )
                END
            WHEN LEN( @text_corrected ) - LEN( REPLACE( @text_corrected, ' ', '' ) ) = 0
            THEN @text_corrected
            WHEN LEN( @text_corrected ) - LEN( REPLACE( @text_corrected, ' ', '' ) ) > 0
            THEN dbo.fn_GetStringPartByDelimiter( @text_corrected, ' ', 1 )
        END;

    SELECT @last_name =
        CASE
            WHEN LEN( @text_corrected ) - LEN( REPLACE( @text_corrected, '@', '' ) ) > 0
            THEN
                CASE
                    WHEN LEN( dbo.fn_GetStringPartByDelimiter( @text_corrected, '@', 1 ) ) - LEN( REPLACE( dbo.fn_GetStringPartByDelimiter( @text_corrected, '@', 1 ), '.', '' ) ) = 0
                    THEN NULL
                    WHEN LEN( dbo.fn_GetStringPartByDelimiter( @text_corrected, '@', 1 ) ) - LEN( REPLACE( dbo.fn_GetStringPartByDelimiter( @text_corrected, '@', 1 ), '.', '' ) ) = 1
                    THEN dbo.fn_ProperCase( dbo.fn_GetStringPartByDelimiter( dbo.fn_GetStringPartByDelimiter( @text_corrected, '@', 1 ), '.', 2 ) )
                    ELSE dbo.fn_ProperCase( dbo.fn_GetStringPartByDelimiter( dbo.fn_GetStringPartByDelimiter( @text_corrected, '@', 1 ), '.', 3 ) )
                END
            WHEN LEN( @text_corrected ) - LEN( REPLACE( @text_corrected, ' ', '' ) ) = 0
            THEN NULL
            WHEN LEN( @text_corrected ) - LEN( REPLACE( @text_corrected, ' ', '' ) ) = 1
            THEN dbo.fn_GetStringPartByDelimiter( @text_corrected, ' ', 2 )
            ELSE
                CASE
                    WHEN LEN( REPLACE( dbo.fn_GetStringPartByDelimiter( @text_corrected, ' ', 2 ), '.', '' ) ) = 1
                    THEN dbo.fn_GetStringPartByDelimiter( @text_corrected, ' ', 3 )
                    WHEN dbo.fn_GetStringPartByDelimiter( @text_corrected, ' ', 2 ) IN( 'van',
                                                                                       'von' )
                    THEN dbo.fn_GetStringPartByDelimiter( @text_corrected, ' ', 2 ) + ' ' + dbo.fn_GetStringPartByDelimiter( @text_corrected, ' ', 3 )
                    ELSE dbo.fn_GetStringPartByDelimiter( @text_corrected, ' ', 2 )
                END
        END;

    SELECT @first_name = REPLACE( REPLACE( ISNULL( @first_name, '' ), ',', '' ), ':', '' );

    SELECT @last_name = REPLACE( REPLACE( ISNULL( @last_name, '' ), ',', '' ), ':', '' );

    SELECT @Result = @first_name + '^' + @last_name;

    RETURN @Result;
END;
GO

fn_email_check

This function checks to verify that the email address passes a validity test based on the allowed characters and format of an email address.

CREATE FUNCTION [DBO].[fn_email_check](
                                           @email VARCHAR( 255 ) )
RETURNS BIT AS
BEGIN
    DECLARE @valid BIT;
    IF @email IS NOT NULL
    SET @email = LOWER( @email );
    SET @valid = 0;
    IF @email LIKE '[a-z,0-9,_,'',-]%@[a-z,0-9,_,-]%.[a-z][a-z]%'
    AND
    LEN( @email ) = LEN( [DBO].[fn_strip_non-email]( @email ) )
    AND
    @email NOT LIKE '%@%@%'
    AND
    CHARINDEX( '.@', @email ) = 0
    AND
    CHARINDEX( '..', @email ) = 0
    AND
    CHARINDEX( ',', @email ) = 0
    AND
    CHARINDEX( ' ', @email ) = 0
    AND
    (
        (
            RIGHT( @email, 1 ) BETWEEN 'a'
            AND
            'z'
        )
        OR
        (
            RIGHT( @email, 1 ) BETWEEN '0'
            AND
            '9'
        )
    )
    SET @valid = 1;
    RETURN @valid;
END;
GO

fn_strip_non_email

This function will remove any characters that aren't allowed in an email address.

CREATE FUNCTION [dbo].[fn_strip_non_email](
                                               @Temp VARCHAR( 1000 ) )
Returns VARCHAR( 1000 ) AS
BEGIN
    DECLARE @KeepValues AS VARCHAR( 50 )
    SET @KeepValues = '%[^a-z,0-9,_,@,.,'',-]%'
    WHILE PatIndex( @KeepValues, @Temp ) > 0
    SET @Temp = Stuff( @Temp, PatIndex( @KeepValues, @Temp ), 1, '' )
    RETURN @Temp
END
GO

fn_url_decode

This function decodes an encoded URL string.

CREATE FUNCTION [dbo].[fn_url_decode](
                                         @url VARCHAR( 3072 ) )
RETURNS VARCHAR( 3072 ) AS
BEGIN
    DECLARE @count INT,
        @c CHAR( 1 ),
        @cenc CHAR( 2 ),
        @i INT,
        @urlReturn VARCHAR( 3072 )
    SET @count = LEN( @url )
    SET @i = 1
    SET @urlReturn = ''
    WHILE( @i <= @count )
    BEGIN
        SET @c = substring( @url, @i, 1 )
        IF @c LIKE '[!%]' ESCAPE '!'
        BEGIN
            SET @cenc = substring( @url, @i + 1, 2 )
            SET @c = CHAR(
                CASE
                WHEN SUBSTRING( @cenc, 1, 1 ) LIKE '[0-9]' THEN
                    CAST( SUBSTRING( @cenc, 1, 1 ) AS INT )
                    ELSE CAST( ASCII( UPPER( SUBSTRING( @cenc, 1, 1 ) ) )-55 AS INT )
                END
                * 16 +
                CASE
                WHEN SUBSTRING( @cenc, 2, 1 ) LIKE '[0-9]' THEN
                    CAST( SUBSTRING( @cenc, 2, 1 ) AS INT )
                    ELSE CAST( ASCII( UPPER( SUBSTRING( @cenc, 2, 1 ) ) )-55 AS INT )
                END
            )
            SET @urlReturn = @urlReturn + @c
            SET @i = @i + 2
        END
        ELSE
        BEGIN
            SET @urlReturn = @urlReturn + @c
        END
        SET @i = @i +1
    END
    RETURN @urlReturn
END
GO

fn_url_encode

This function encodes a URL string.

CREATE FUNCTION [dbo].[fn_url_encode](
                                         @url NVARCHAR( 1024 ) )
RETURNS NVARCHAR( 3072 ) AS
BEGIN
    DECLARE @count INT,
        @c NCHAR( 1 ),
        @i INT,
        @urlReturn NVARCHAR( 3072 )
    SET @count = LEN( @url )
    SET @i = 1
    SET @urlReturn = ''
    WHILE( @i <= @count )
    BEGIN
        SET @c = SUBSTRING( @url, @i, 1 )
        IF @c LIKE N'[A-Za-z0-9()''*\-._!~]' COLLATE Latin1_General_BIN ESCAPE N'\' COLLATE Latin1_General_BIN
        BEGIN
            SET @urlReturn = @urlReturn + @c
        END
        ELSE
        BEGIN
            SET @urlReturn = @urlReturn + '%' + SUBSTRING( sys.fn_varbintohexstr( CAST( @c AS VARBINARY( MAX ) ) ), 3, 2 ) + ISNULL( NULLIF( SUBSTRING( sys.fn_varbintohexstr( CAST( @c AS VARBINARY( MAX ) ) ), 5, 2 ), '00' ), '' )
        END
        SET @i = @i +1
    END
    RETURN @urlReturn
END
GO