cant fix Error converting data type varchar to numeric.

Question

go
use auditdb
go
create table test (phone Varchar(10)null)
go
insert into test Values (9002624142)
insert into test Values ('50012765428')

select top 10 * from test

select *from test WHERE Phone ='50012765428'
select *from test WHERE Phone =9002624142

select *from test WHERE Phone =50012765428
select *from test WHERE Phone ='9002624142'

above works fine when i create in UAT and PROD, and i have same kind of table existing

but when i try to run below on Existing table, i get Error

table ABC-column varchar(10) Null,

Select *from ABC WHERE Phone =50012765428

Msg 8114, Level 16, State 5, Line 7
Error converting data type varchar to numeric.

runs fine for below::

Select *from ABC WHERE Phone ='50012765428'

there are no index, keys, triggers which may block-

kindly please help on how to fix the issue.

Answer 1

Hello,

You probably get that error because sql server is trying to convert the column Phone (from VARCHAR) to a number, and in your existing table, some records contains some extra characters that fail the conversion.
Try the following to check :
Select * from ABC WHERE ISNUMERIC(Phone)<>0

Answer 2

Hi @NickK ,

Thank you so much for posting here in Microsoft Q&A.

In order to avoid this kind of issue, there are two options:

Option One: Add CAST or CONVERT function as below:

Select * from ABC WHERE CAST(Phone as NUMERIC) =50012765428  
Select * from ABC WHERE TRY_CAST(Phone as NUMERIC) =50012765428  
Select * from ABC WHERE CONVERT(NUMERIC,Phone) =50012765428  
Select * from ABC WHERE TRY_CONVERT(NUMERIC,Phone) =50012765428  

Note: The TRY_CAST() function works just like CAST() except that if the data can’t be converted, it returns null (instead of throwing an error like CAST() does).

Option Two: Change the type of phone column of existing table ABC if all values of this column are numbers and it is permitted per your requirement.

alter table ABC ALTER COLUMN Phone NUMERIC  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi

@MelissaMa-MSFT , Option Two is not perfect because of numeric type loose 0 or + prefix. @SM has a right at all. The requirements are important too.

Please consider that solution:

declare @Phone varchar(10)

-- casting of int value to varchar variable in separated line, 50012765428 became from application
select @Phone = 50012765428

SELECT * FROM ABC WHERE Phone = @Phone

By the way, @NickK length of 50012765428 is greater than 10, so you should consider increasing Phone column's type

Regards

Answer 4

Thanks all for the Help.