How to convert varchar to Numeric in SQL?

Question

I am trying to make an SQL query, but it returns the following error: "Error Converting data type varchar to Numeric."

SELECT Tabela1.CPF, Tabela2.CPF
FROM Tabela1
INNER JOIN Tabela2
ON Tabela1.CPF = Tabela2.CPF

I verified that the CPF field of Table 1 is Numeric and the CPF field of Table 2 is varchar. I did the varchar to Numeric type conversion using the CAST() function, but continued to get the same error message: "Error Converting data type varchar to Numeric.":

SELECT Tabela1.CPF, CAST(Tabela2.CPF AS NUMERIC)
FROM Tabela1
INNER JOIN Tabela2
ON Tabela1.CPF = CAST(Tabela2.CPF AS NUMERIC)

Why the conversion is not working?

Answer 1

What is occurring is an automatic conversion of data type, called implicit conversion by Microsoft. Details in article "The dangers of automatic data type conversion”.

You commented that the CPF column of Table 1 is declared as Numeric, but does not clarify what this means: integer, bigint, Numeric(11)?

Here’s a suggestion that converts CPF to text and adding zeros to the left if necessary:

-- código #1
SELECT T1.CPF, T2.CPF
  from Tabela1 as T1
       inner join Tabela2 as T2 
       on right ('00000000000' + cast(T1.CPF as varchar(11)), 11) = T2.CPF;

Aware that the junction predicate was non sargable. Details in the article "Building Efficient T-SQL Code”.

Answer 2

Surely in the table where the CPF is varchar there is some record that has a value of the non-numeric type. Pay attention to the spaces in the field. You can make use of the functions Ltrim() and Rtrim() to make sure that the spaces will be deleted.

SELECT T1.CPF, T2.CPF
FROM Tabela1 as T1
JOIN Tabela2 as T2 ON cast(LTrim(RTrim(T1.CPF)) as numeric(11,0))=T2.CPF

And another thing. To check which of your data may present some inconsistency, you can use this little trick instead:

SELECT LTrim(RTrim(T1.CPF)),T1.CPF,T1.*
FROM Tabela1 as T1
WHERE LTrim(RTrim(T1.CPF))!=T1.CPF

Answer 3

Good afternoon! Try to convert both to VARCHAR and compare, probably some CPF is with characters different than expected. If this does not work, try to make a TOP 10 and check if any data is returned. Finally, it may be imputing these values in temporary tables and comparing them later. Abs.