Conversion from VARCHAR to INT

Question

Conversion from VARCHAR to INT

Navigation

#1 by (5 votes)

2

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 zeroes to " Left " the connection does not work.

I thought of converting them to INT but it is not working, I think it's 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 appearing:

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

sql sql-server

asked by anonymous 12.07.2017 / 17:06

1 answer

How to check for Apache configuration errors before restarting? How to return these values outside the Array?

score 5 · Accepted Answer

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

Below is 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 execute QUERY like this:

    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 running the query, if you encounter this error message: "Error converting data type varchar to bigin" , then there is probably a não numérico value in any of the columns, to resolve this case, try to run the see below, to fix this problem, but that way, talvez your INNER JOIN does not work the way you want.

    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 )