February 17rd, 2015
19
SQL Server Query Development Data Formatting and Validation Database

Validating CPF, CNPJ, Email, Phone, and Zip Code in SQL Server

Views: 22.873 views

Reading Time: 2 minutes

Hello people,
Alright?

Today I will be introducing some functions that will help them improve validations in their routines, such as validating a CPF from your base is valid or not.

Zip Validation
This is a simple validation, which only verifies that the number of characters is correct and that all of the entered string has 8 numeric characters. For stronger validation, I suggest that a database be purchased from the Post Office and the validation function should consult with that database to confirm CEP data and veracity.

CREATE FUNCTION [dbo].[fncVerifica_Cep](@Nr_Cep CHAR(8))
RETURNS BIT
AS 
BEGIN

    DECLARE @chr CHAR, @tamanho INT
    
    -- Verifica se possui 8 caracteres
    IF (LEN(@Nr_Cep) < 8)
        RETURN 0
        
    WHILE (LEN(@Nr_Cep) > 0)
    BEGIN
    
        SELECT @tamanho = LEN(@Nr_Cep), @chr = LEFT(@Nr_Cep,1)
        
        -- Verifica se o número informado possui apenas números
        IF CHARINDEX(@chr,'0123456789') = 0
        BEGIN
            RETURN 0
            BREAK
        END
        
        SET @Nr_Cep = STUFF(@Nr_Cep,1,1,'') -- retira o primeiro dígito
        
    END
    
    RETURN 1
    
END

CREATE FUNCTION [dbo].[fncVerifica_Cep](@Nr_Cep CHAR(8))

RETURNS BIT

BEGIN

DECLARE @chr CHAR, @tamanho INT

-- Verifica se possui 8 caracteres

IF (LEN(@Nr_Cep) < 8)

RETURN 0

WHILE (LEN(@Nr_Cep) > 0)

BEGIN

SELECT @tamanho = LEN(@Nr_Cep), @chr = LEFT(@Nr_Cep,1)

-- Verifica se o número informado possui apenas números

IF CHARINDEX(@chr,'0123456789') = 0

BEGIN

RETURN 0

BREAK

END

SET @Nr_Cep = STUFF(@Nr_Cep,1,1,'') -- retira o primeiro dígito

END

RETURN 1

END

Email Validation
This function performs an address validation using an email validation mask. Strings like “[email protected]”Or“ test ”will not be validated.

CREATE FUNCTION dbo.fncValidarEmail(@Ds_Email varchar(max))
RETURNS BIT
AS BEGIN

    DECLARE @Retorno BIT = 0

    SELECT @Retorno = 1
    WHERE @Ds_Email NOT LIKE '%[^a-z,0-9,@,.,_,-]%'
    AND @Ds_Email LIKE '%_@_%_.__%'
    AND @Ds_Email NOT LIKE '%_@@_%_.__%'

    RETURN @Retorno

END

CREATE FUNCTION dbo.fncValidarEmail(@Ds_Email varchar(max))

RETURNS BIT

AS BEGIN

DECLARE @Retorno BIT = 0

SELECT @Retorno = 1

WHERE @Ds_Email NOT LIKE '%[^a-z,0-9,@,.,_,-]%'

AND @Ds_Email LIKE '%_@_%_.__%'

AND @Ds_Email NOT LIKE '%_@@_%_.__%'

RETURN @Retorno

END

CPF Validation
This function will validate the number of digits of the CPF (must have 11), validate that the informed CPF is not in the list of repeated CPF's, but that “pass” through the standard validations and does the calculation and validation of the informed CPF to identify if it is valid or not.

CREATE FUNCTION [dbo].[fncValida_CPF](
    @Nr_Documento VARCHAR(11)
)
RETURNS BIT -- 1 = válido, 0 = inválido
WITH SCHEMABINDING
BEGIN

    DECLARE
        @Contador_1 INT,
        @Contador_2 INT,
        @Digito_1 INT,
        @Digito_2 INT,
        @Nr_Documento_Aux VARCHAR(11)

    -- Remove espaços em branco
    SET @Nr_Documento_Aux = LTRIM(RTRIM(@Nr_Documento))
    SET @Digito_1 = 0


    -- Remove os números que funcionam como validação para CPF, pois eles "passam" pela regra de validação
    IF (@Nr_Documento_Aux IN ('00000000000', '11111111111', '22222222222', '33333333333', '44444444444', '55555555555', '66666666666', '77777777777', '88888888888', '99999999999', '12345678909'))
        RETURN 0


    -- Verifica se possui apenas 11 caracteres
    IF (LEN(@Nr_Documento_Aux) <> 11)
        RETURN 0
    ELSE 
    BEGIN

        -- Cálculo do segundo dígito
        SET @Nr_Documento_Aux = SUBSTRING(@Nr_Documento_Aux, 1, 9)

        SET @Contador_1 = 2

        WHILE (@Contador_1 < = 10)
        BEGIN 
            SET @Digito_1 = @Digito_1 + (@Contador_1 * CAST(SUBSTRING(@Nr_Documento_Aux, 11 - @Contador_1, 1) as int))
            SET @Contador_1 = @Contador_1 + 1
        end 

        SET @Digito_1 = @Digito_1 - (@Digito_1/11)*11

        IF (@Digito_1 <= 1)
            SET @Digito_1 = 0
        ELSE 
            SET @Digito_1 = 11 - @Digito_1

        SET @Nr_Documento_Aux = @Nr_Documento_Aux + CAST(@Digito_1 AS VARCHAR(1))

        IF (@Nr_Documento_Aux <> SUBSTRING(@Nr_Documento, 1, 10))
            RETURN 0
        ELSE BEGIN 
        
            -- Cálculo do segundo dígito
            SET @Digito_2 = 0
            SET @Contador_2 = 2

            WHILE (@Contador_2 < = 11)
            BEGIN 
                SET @Digito_2 = @Digito_2 + (@Contador_2 * CAST(SUBSTRING(@Nr_Documento_Aux, 12 - @Contador_2, 1) AS INT))
                SET @Contador_2 = @Contador_2 + 1
            end 

            SET @Digito_2 = @Digito_2 - (@Digito_2/11)*11

            IF (@Digito_2 < 2)
                SET @Digito_2 = 0
            ELSE 
                SET @Digito_2 = 11 - @Digito_2

            SET @Nr_Documento_Aux = @Nr_Documento_Aux + CAST(@Digito_2 AS VARCHAR(1))

            IF (@Nr_Documento_Aux <> @Nr_Documento)
                RETURN 0
                
        END
    END 
    
	RETURN 1
    
END

CREATE FUNCTION [dbo].[fncValida_CPF](

@Nr_Documento VARCHAR(11)

)

RETURNS BIT -- 1 = válido, 0 = inválido

WITH SCHEMABINDING

BEGIN

DECLARE

@Contador_1 INT,

@Contador_2 INT,

@Digito_1 INT,

@Digito_2 INT,

@Nr_Documento_Aux VARCHAR(11)

-- Remove espaços em branco

SET @Nr_Documento_Aux = LTRIM(RTRIM(@Nr_Documento))

SET @Digito_1 = 0

-- Remove os números que funcionam como validação para CPF, pois eles "passam" pela regra de validação

IF (@Nr_Documento_Aux IN ('00000000000', '11111111111', '22222222222', '33333333333', '44444444444', '55555555555', '66666666666', '77777777777', '88888888888', '99999999999', '12345678909'))

RETURN 0

-- Verifica se possui apenas 11 caracteres

IF (LEN(@Nr_Documento_Aux) <> 11)

RETURN 0

ELSE

BEGIN

-- Cálculo do segundo dígito

SET @Nr_Documento_Aux = SUBSTRING(@Nr_Documento_Aux, 1, 9)

SET @Contador_1 = 2

WHILE (@Contador_1 < = 10)

BEGIN

SET @Digito_1 = @Digito_1 + (@Contador_1 * CAST(SUBSTRING(@Nr_Documento_Aux, 11 - @Contador_1, 1) as int))

SET @Contador_1 = @Contador_1 + 1

end

SET @Digito_1 = @Digito_1 - (@Digito_1/11)*11

IF (@Digito_1 <= 1)

SET @Digito_1 = 0

ELSE

SET @Digito_1 = 11 - @Digito_1

SET @Nr_Documento_Aux = @Nr_Documento_Aux + CAST(@Digito_1 AS VARCHAR(1))

IF (@Nr_Documento_Aux <> SUBSTRING(@Nr_Documento, 1, 10))

RETURN 0

ELSE BEGIN

-- Cálculo do segundo dígito

SET @Digito_2 = 0

SET @Contador_2 = 2

WHILE (@Contador_2 < = 11)

BEGIN

SET @Digito_2 = @Digito_2 + (@Contador_2 * CAST(SUBSTRING(@Nr_Documento_Aux, 12 - @Contador_2, 1) AS INT))

SET @Contador_2 = @Contador_2 + 1

end

SET @Digito_2 = @Digito_2 - (@Digito_2/11)*11

IF (@Digito_2 < 2)

SET @Digito_2 = 0

ELSE

SET @Digito_2 = 11 - @Digito_2

SET @Nr_Documento_Aux = @Nr_Documento_Aux + CAST(@Digito_2 AS VARCHAR(1))

IF (@Nr_Documento_Aux <> @Nr_Documento)

RETURN 0

END

RETURN 1

END

Validation of CNPJ
This function will validate the number of CNPJ digits (must have 14) and perform the calculation and validation of the entered CNPJ to identify whether or not it is valid.

CREATE FUNCTION [dbo].[fncValida_CNPJ] ( @CNPJ VARCHAR(14) )
RETURNS BIT
AS
BEGIN

    DECLARE
        @INDICE INT,
        @SOMA INT,
        @DIG1 INT,
        @DIG2 INT,
        @VAR1 INT,
        @VAR2 INT,
        @RESULTADO CHAR(1)
 

    SET @SOMA = 0
    SET @INDICE = 1
    SET @RESULTADO = 0
    SET @VAR1 = 5 /* 1a Parte do Algorítimo começando de "5" */

    WHILE ( @INDICE < = 4 )
    BEGIN
        SET @SOMA = @SOMA + CONVERT(INT, SUBSTRING(@CNPJ, @INDICE, 1)) * @VAR1
        SET @INDICE = @INDICE + 1 /* Navegando um-a-um até < = 4, as quatro primeira posições */
        SET @VAR1 = @VAR1 - 1       /* subtraindo o algorítimo de 5 até 2 */
    END

       
    SET @VAR2 = 9
    WHILE ( @INDICE <= 12 )
    BEGIN
        SET @SOMA = @SOMA + CONVERT(INT, SUBSTRING(@CNPJ, @INDICE, 1)) * @VAR2
        SET @INDICE = @INDICE + 1
        SET @VAR2 = @VAR2 - 1            
    END

    SET @DIG1 = ( @SOMA % 11 )


   /* SE O RESTO DA DIVISÃO FOR < 2, O DIGITO = 0 */
    IF @DIG1 < 2
        SET @DIG1 = 0;
    ELSE /* SE O RESTO DA DIVISÃO NÃO FOR < 2*/
        SET @DIG1 = 11 - ( @SOMA % 11 );


    SET @INDICE = 1
    SET @SOMA = 0
    SET @VAR1 = 6 /* 2a Parte do Algorítimo começando de "6" */
    SET @RESULTADO = 0


    WHILE ( @INDICE <= 5 )
    BEGIN
        SET @SOMA = @SOMA + CONVERT(INT, SUBSTRING(@CNPJ, @INDICE, 1)) * @VAR1
        SET @INDICE = @INDICE + 1 /* Navegando um-a-um até < = 5, as quatro primeira posições */
        SET @VAR1 = @VAR1 - 1       /* subtraindo o algorítimo de 6 até 2 */
    END



    /* CÁLCULO DA 2ª PARTE DO ALGORÍTIOM 98765432 */
    SET @VAR2 = 9
    WHILE ( @INDICE <= 13 )
    BEGIN
        SET @SOMA = @SOMA + CONVERT(INT, SUBSTRING(@CNPJ, @INDICE, 1)) * @VAR2
        SET @INDICE = @INDICE + 1
        SET @VAR2 = @VAR2 - 1            
    END

 
    SET @DIG2 = ( @SOMA % 11 )

 
   /* SE O RESTO DA DIVISÃO FOR < 2, O DIGITO = 0 */

    IF @DIG2 < 2
        SET @DIG2 = 0;

    ELSE /* SE O RESTO DA DIVISÃO NÃO FOR < 2*/
        SET @DIG2 = 11 - ( @SOMA % 11 );


    IF ( @DIG1 = SUBSTRING(@CNPJ, LEN(@CNPJ) - 1, 1) ) AND ( @DIG2 = SUBSTRING(@CNPJ, LEN(@CNPJ), 1) )
        SET @RESULTADO = 1
    ELSE
        SET @RESULTADO = 0

 
 
    RETURN @RESULTADO
 

END

CREATE FUNCTION [dbo].[fncValida_CNPJ] ( @CNPJ VARCHAR(14) )

RETURNS BIT

BEGIN

DECLARE

@INDICE INT,

@SOMA INT,

@DIG1 INT,

@DIG2 INT,

@VAR1 INT,

@VAR2 INT,

@RESULTADO CHAR(1)

SET @SOMA = 0

SET @INDICE = 1

SET @RESULTADO = 0

SET @VAR1 = 5 /* 1a Parte do Algorítimo começando de "5" */

WHILE ( @INDICE < = 4 )

BEGIN

SET @SOMA = @SOMA + CONVERT(INT, SUBSTRING(@CNPJ, @INDICE, 1)) * @VAR1

SET @INDICE = @INDICE + 1 /* Navegando um-a-um até < = 4, as quatro primeira posições */

SET @VAR1 = @VAR1 - 1 /* subtraindo o algorítimo de 5 até 2 */

END

SET @VAR2 = 9

WHILE ( @INDICE <= 12 )

BEGIN

SET @SOMA = @SOMA + CONVERT(INT, SUBSTRING(@CNPJ, @INDICE, 1)) * @VAR2

SET @INDICE = @INDICE + 1

SET @VAR2 = @VAR2 - 1

END

SET @DIG1 = ( @SOMA % 11 )

/* SE O RESTO DA DIVISÃO FOR < 2, O DIGITO = 0 */

IF @DIG1 < 2

SET @DIG1 = 0;

ELSE /* SE O RESTO DA DIVISÃO NÃO FOR < 2*/

SET @DIG1 = 11 - ( @SOMA % 11 );

SET @INDICE = 1

SET @SOMA = 0

SET @VAR1 = 6 /* 2a Parte do Algorítimo começando de "6" */

SET @RESULTADO = 0

WHILE ( @INDICE <= 5 )

BEGIN

SET @SOMA = @SOMA + CONVERT(INT, SUBSTRING(@CNPJ, @INDICE, 1)) * @VAR1

SET @INDICE = @INDICE + 1 /* Navegando um-a-um até < = 5, as quatro primeira posições */

SET @VAR1 = @VAR1 - 1 /* subtraindo o algorítimo de 6 até 2 */

END

/* CÁLCULO DA 2ª PARTE DO ALGORÍTIOM 98765432 */

SET @VAR2 = 9

WHILE ( @INDICE <= 13 )

BEGIN

SET @SOMA = @SOMA + CONVERT(INT, SUBSTRING(@CNPJ, @INDICE, 1)) * @VAR2

SET @INDICE = @INDICE + 1

SET @VAR2 = @VAR2 - 1

END

SET @DIG2 = ( @SOMA % 11 )

/* SE O RESTO DA DIVISÃO FOR < 2, O DIGITO = 0 */

IF @DIG2 < 2

SET @DIG2 = 0;

ELSE /* SE O RESTO DA DIVISÃO NÃO FOR < 2*/

SET @DIG2 = 11 - ( @SOMA % 11 );

IF ( @DIG1 = SUBSTRING(@CNPJ, LEN(@CNPJ) - 1, 1) ) AND ( @DIG2 = SUBSTRING(@CNPJ, LEN(@CNPJ), 1) )

SET @RESULTADO = 1

ELSE

SET @RESULTADO = 0

RETURN @RESULTADO

END

CPF and CNPJ Validation
This function aims to perform the CPF and CNPJ validation, using the above functions depending on the number of characters of the entered Document No.

CREATE FUNCTION [dbo].[fncValida_Documento] ( 
    @Nr_Documento VARCHAR(14) 
)
RETURNS BIT
AS BEGIN

    DECLARE @Retorno BIT = 0


    IF (LEN(@Nr_Documento) = 11)
    BEGIN

        -- Valida CPF
        IF (@Nr_Documento IN ('00000000000', '11111111111', '22222222222', '33333333333', '44444444444', '55555555555', '66666666666', '77777777777', '88888888888', '99999999999', '12345678909'))
            SET @Retorno = 0
        ELSE
            SET @Retorno = CLR.dbo.fncValida_CPF(@Nr_Documento)

    END
    ELSE BEGIN 
        
        -- Valida CNPJ
        IF (LEN(@Nr_Documento) = 14)
            SET @Retorno = CLR.dbo.fncValida_CNPJ(@Nr_Documento)
        ELSE
            SET @Retorno = 0

    END


    RETURN @Retorno
	

END

CREATE FUNCTION [dbo].[fncValida_Documento] (

@Nr_Documento VARCHAR(14)

)

RETURNS BIT

AS BEGIN

DECLARE @Retorno BIT = 0

IF (LEN(@Nr_Documento) = 11)

BEGIN

-- Valida CPF

IF (@Nr_Documento IN ('00000000000', '11111111111', '22222222222', '33333333333', '44444444444', '55555555555', '66666666666', '77777777777', '88888888888', '99999999999', '12345678909'))

SET @Retorno = 0

ELSE

SET @Retorno = CLR.dbo.fncValida_CPF(@Nr_Documento)

END

ELSE BEGIN

-- Valida CNPJ

IF (LEN(@Nr_Documento) = 14)

SET @Retorno = CLR.dbo.fncValida_CNPJ(@Nr_Documento)

ELSE

SET @Retorno = 0

END

RETURN @Retorno

END

Phone Validation
This function will check the number of digits in the phone and if the number entered is not just repeated numbers.

CREATE FUNCTION [dbo].[fncValida_Telefone] (@Nr_Telefone VARCHAR(15))
RETURNS BIT
AS
BEGIN
	
    DECLARE 
        @Retorno BIT = 1,
        @Nr_Telefone_Valida VARCHAR(15)


    -- Verifica se possui caracteres não numéricos
    SET @Nr_Telefone_Valida = dbo.fncRecupera_Numeros(@Nr_Telefone)

    IF (@Nr_Telefone != @Nr_Telefone_Valida)
        SET @Retorno = 0


    -- Verifica a quantidade de digitos
    SET @Nr_Telefone_Valida = (CASE 
                                    WHEN LEN(@Nr_Telefone) = 8 THEN @Nr_Telefone
                                    WHEN LEN(@Nr_Telefone) = 9 THEN @Nr_Telefone
                                    WHEN LEN(@Nr_Telefone) = 10 THEN RIGHT(@Nr_Telefone,8)
                                    WHEN LEN(@Nr_Telefone) = 11 THEN RIGHT(@Nr_Telefone,9) 
                                    ELSE NULL
                               END)


    -- Verifica se possui apenas números repetidos
    IF(RIGHT(@Nr_Telefone_Valida, 8) IN ('99999999','88888888','77777777','66666666','55555555','44444444','33333333','22222222','11111111','00000000'))
        SET @Retorno = 0 
        
        
    -- Verifica se é string vazia
    IF (@Nr_Telefone_Valida IS NULL)
        SET @Retorno = 0							


    RETURN @Retorno

	
END

CREATE FUNCTION [dbo].[fncValida_Telefone] (@Nr_Telefone VARCHAR(15))

RETURNS BIT

BEGIN

DECLARE

@Retorno BIT = 1,

@Nr_Telefone_Valida VARCHAR(15)

-- Verifica se possui caracteres não numéricos

SET @Nr_Telefone_Valida = dbo.fncRecupera_Numeros(@Nr_Telefone)

IF (@Nr_Telefone != @Nr_Telefone_Valida)

SET @Retorno = 0

-- Verifica a quantidade de digitos

SET @Nr_Telefone_Valida = (CASE

WHEN LEN(@Nr_Telefone) = 8 THEN @Nr_Telefone

WHEN LEN(@Nr_Telefone) = 9 THEN @Nr_Telefone

WHEN LEN(@Nr_Telefone) = 10 THEN RIGHT(@Nr_Telefone,8)

WHEN LEN(@Nr_Telefone) = 11 THEN RIGHT(@Nr_Telefone,9)

ELSE NULL

END)

-- Verifica se possui apenas números repetidos

IF(RIGHT(@Nr_Telefone_Valida, 8) IN ('99999999','88888888','77777777','66666666','55555555','44444444','33333333','22222222','11111111','00000000'))

SET @Retorno = 0

-- Verifica se é string vazia

IF (@Nr_Telefone_Valida IS NULL)

SET @Retorno = 0

RETURN @Retorno

END

Extra function
You may have noticed that the function to validate phone has a dependency, which is fncRecover_Number. This function is intended to return only numeric characters (0 to 9) and a string, removing strings, accents, special characters, etc.

CREATE FUNCTION [dbo].[fncRecupera_Numeros] ( @str VARCHAR(500) )
RETURNS VARCHAR(500)
BEGIN  

    DECLARE @startingIndex INT  
    SET @startingIndex = 0  
    
    WHILE (1 = 1)
    BEGIN  
    
        SET @startingIndex = PATINDEX('%[^0-9]%', @str)  
        IF @startingIndex <> 0
            SET @str = REPLACE(@str, SUBSTRING(@str, @startingIndex, 1), '')  
        ELSE
            BREAK
            
    END  
    
    
    RETURN @str  
    
    
END

CREATE FUNCTION [dbo].[fncRecupera_Numeros] ( @str VARCHAR(500) )

RETURNS VARCHAR(500)

BEGIN

DECLARE @startingIndex INT

SET @startingIndex = 0

WHILE (1 = 1)

BEGIN

SET @startingIndex = PATINDEX('%[^0-9]%', @str)

IF @startingIndex <> 0

SET @str = REPLACE(@str, SUBSTRING(@str, @startingIndex, 1), '')

ELSE

BREAK

END

RETURN @str

END

If you want to download the file with all the scripts presented directly, click on the link
SQL Server - Validation Functions

That's it.
To the next.

Tags: document Email database flail phone sql server function server function sql t-sql validation validate cpf cnpj

Lee Ewerton Aguiar said:

December 15 from 2020 to 17: 16

Good afternoon, I need a PROCEDURE to validate RENAVAM

Reply
Fabio Sabampato said:

December 11 from 2019 to 16: 03

Very good and very useful!
Congratulations and thank you very much!

I work with ETL and take heavily polluted banks, so I took the liberty of adding the condition that checks if the field is not empty:

- Check if it is NULL
IF ((@Nr_Document_Aux) IS NULL)
RETURN 0

[]'s

Reply
German Sandro said:

26 July 2019 to 18: 22

Dirceu ball show, I used the validated CPF and CNPJ.
I just needed to put an adjustment to exclude possible punctuation characters.
I put at the beginning of fncValida_Document:

DECLARE @DOC VARCHAR (14) = ”
; WITH SPLIT AS (
SELECT 1 AS ID, SUBSTRING (@Nr_Document, 1, 1) AS DIGITAL
UNION ALL
SELECT ID + 1, SUBSTRING (@Nr_Document, ID + 1, 1)
FROM SPLIT
WHERE ID <LEN (@Nr_Document)
)
SELECT @DOC + = ALGARISM FROM SPLIT WHERE ALGARISM LIKE '[0-9]'
SET @Nr_Document = @DOC

Reply
- Dirceu Resende said:
  
  19 from 2019 from September to 00: 28
  
  Oops, I'm glad you liked it 🙂
  
  Reply
João Paulo Silva said:

18 from 2018 from September to 18: 09

Show your functions, however emails with '-' as [email protected] e [email protected] are coming back as invalid.

Can you tell me why?

Reply
- Dirceu Resende said:
  
  18 from 2018 from September to 20: 41
  
  John Paul,
  Thanks for the feedback. I validated the script and I really forgot to add the character “-“. Now the script is fine. Hug!
  
  Reply
Alex Deilon said:

5 April 2018 16 gies: 07

The e-mail validation function is letting e-mails like 'alex@@gmail.com' pass through, it solves if you add the filter (AND @Email NOT LIKE '%_@@_%_.__%').

Reply
Vicente Neto said:

8 March 2018 to 11: 13

Very good even for sharing, just giving an error ”[Err] 42000 - [SQL Server] Cannot assign a default value to a local variable.
42000 - [SQL Server] Must declare the variable '@Retorno'. ”When I try to install CPF and CNPJ Validation

Reply
Guathan Medeiros said:

21 July 2017 to 14: 45

The e-mail [email protected] returns valid, and is incorrect due to the dot (.) after the at sign (@)

Reply
Ivan (@ivanfla) said:

4 April 2017 17 gies: 52

Good morning mate. I liked the validations. In email I had to make a small adjustment to pass email with _ (underline):

CREATE FUNCTION dbo.fncValidateEmail (@Ds_Email varchar (max))
RETURNS BIT
AS BEGIN

DECLARE @BIT = 0 Return

SELECT @Return = 1
WHERE @Ds_Email NOT LIKE '% [^ az, 0-9, @, ._-]%'
AND @Ds_Email LIKE '%_@_%_.__%'

RETURN @Return

END

Reply
- Dirceu Resende said:
  
  6 April 2017 15 gies: 40
  
  Ivan,
  Good afternoon.
  
  Thanks for the feedback and the underline tip (_). I already changed the post to add this character in the validation.
  
  Reply
Thiago Pedro said:

17 January 2017 to 04: 32

The email validation routine by expression does not recognize, for example, [email protected] as valid!

Reply
- Dirceu Resende said:
  
  17 January 2017 to 10: 32
  
  Thiago,
  Good day.
  
  Thank you for visiting and for your feedback. I already corrected the rule in the post to include the character “-“.
  
  Thank you!
  
  Reply
André Fernandes said:

15 from 2016 to 21 at 36: XNUMX

Dirceu now went! Your blog is a show of the ball… Teaches you how to make some packages in the integration with reports in Reporting Services… .and this part of ETL is very interesting and everyone only teaches basics… .what is your channel on youtube?

Reply
André Fernandes said:

12 from 2016 to 20 at 36: XNUMX

Dirceu all functions I normally ran my sql 2012 but the phone (extra function) is returning errors ...

Msg 4145, Level 15, State 1, Procedure fncRecover_Numbers, Line 12
An expression of non-boolean type specified in a context where a condition is expected, near ';'.
Msg 156, Level 15, State 1, Procedure fncRecover_Numbers, Line 14
Incorrect syntax near the keyword 'ELSE'.

Reply
- Dirceu Resende said:
  
  13 from 2016 to 09 at 20: XNUMX
  
  André,
  Thank you for your visit and feedback.
  
  I made a change to the Syntax Highlighting plugin and some old posts have a problem with “<" and ">” being replaced by their respective HTML entities (“<” and “>”)
  
  I updated the codes in this post and now you will be able to compile the function.
  
  Hug.
  
  Reply
Cristian Richard Kulessa said:

5 May 2016 to 18: 59

Is not correctly validating CNPJ starting with '0'

Reply
- Dirceu Resende said:
  
  5 May 2016 to 19: 03
  
  Cristian, could you give me an example, please?
  
  Reply