How to convert varchar to Numeric in SQL?

Asked

Viewed 6,516 times

0

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?

  • 2

    You probably have some data that is not just numbers... That’s why of this mistake.

  • 1

    Convert the number to char this does not give error , use also a Trim , the performance should be impaired.

  • I converted Numeric to scan and managed to run the query successfully.

  • In the CPF column of Tabela2 there are only digits or digits and "." and "-"? Post data sample...

3 answers

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”.

1


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

0

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.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.