Conversion from VARCHAR to INT

Question

I’m trying to do INNER JOIN but the fields are in VARCHAR.

In a field I have cod_authorization = 749831164

And in the other field I have num_aut = 000749831164

Because of the zeroes "Left" connection does not work.

I thought I’d convert them to INT but it’s not working, I believe it is some syntax error.

SELECT 

controle.num_aut,estoque.cod_autorizacao 

FROM cad_controle AS controle
INNER JOIN estoque AS estoque WITH(NOLOCK) ON CAST(estoque.cod_autorizacao AS INT)=CAST(controle.num_aut AS INT)

Error that appears:

The conversion of the varchar value '9977613700' overflowed an int column.

Answer 1

The error is happening because you are converting a value that does not fit the type INT, then ends up giving overflow in conversion. No SQL Server, there are 4 types of variables to treat integers, they are they: TINYINT, SMALLINT, INT and BIGINT.

Below follows the maximum values supported by these types:

• TINYINT: 0 to 255.
• SMALLINT: -32,768 to 32,767.
• INT: -2,147,483,648 to 2,147,483,647
• BIGINT: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

So, to solve your problem, try to run the QUERY thus:

    SELECT
           controle.num_aut ,
           estoque.cod_autorizacao 
      FROM cad_controle AS controle
INNER JOIN estoque AS estoque WITH ( NOLOCK )
        ON CONVERT ( BIGINT , estoque.cod_autorizacao ) = CONVERT ( BIGINT , controle.num_aut )

---

Editing:

When executing the query, if you come across this error message: "Error converting data type varchar to bigin", then there is probably a value não numérico in any of the columns, to solve this case, try to run the query below, to fix this problem, only that way, talvez your INNER JOIN doesn’t work the way you want it to.

    SELECT
           controle.num_aut ,
           estoque.cod_autorizacao 
      FROM cad_controle AS controle
INNER JOIN estoque AS estoque WITH ( NOLOCK )
        ON 
           ( CASE WHEN ( ISNUMERIC ( estoque.cod_autorizacao ) = 1 ) THEN CONVERT ( BIGINT , estoque.cod_autorizacao ) ELSE 0 END ) =
           ( CASE WHEN ( ISNUMERIC ( controle.num_aut ) = 1 ) THEN CONVERT ( BIGINT , controle.num_aut ) ELSE 0 END )