CODEWITHSUNDEEP
sql
DKCroat
DKCroat

Reputation: 357

When trying to convert column data type NVARCHAR to INT getting error

When trying to convert column data type NAVCHAR to INT receiving error. Simple code below:

SELECT [phone], FORMAT(CAST(PHONE AS int),'(000)-000-0000)')
FROM Sales.Customers

Upvotes: 0

Views: 67

Answers (3)

JC Borlagdan
JC Borlagdan

Reputation: 3638

Your query should be something like this: 

SELECT [phone], FORMAT(CAST(REPLACE([phone],'-','')AS INT), '(000)-000-0000)')
FROM Sales.Customers

So if there's an invalid character to be converted to in (for this scenario dash is the one to be replaced), it will replace it with blank then format it with the format you want.

ex: '030-3456789' this will become (030)-345-6789

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I would suggest using TRY_CAST():

SELECT [phone],
       FORMAT(TRY_CAST(PHONE AS int), '(000)-000-0000)')
FROM Sales.Customers;

This returns NULL if the value cannot be converted -- which is better than getting an error.

Upvotes: 0

SQLRaptor
SQLRaptor

Reputation: 681

Seems like you have rows with phone numbers stored as strings, which can't be converted to an int. To find out which, execute:

SELECT *
FROM   Sales.Customers
WHERE  ISNUMERIC(phone) = 0;

HTH

Upvotes: 0

Related Questions