SQL Server: Error converting data type varchar to numeric (Strange Behaviour)

Question

I'm working on a legacy system using SQL Server in 2000 compatibility mode. There's a stored procedure that selects from a query into a virtual table.

When I run the query, I get the following error:

Error converting data type varchar to numeric

which initially tells me that something stringy is trying to make its way into a numeric column.

To debug, I created the virtual table as a physical table and started eliminating each column.

The culprit column is called accnum (which stores a bank account number, which has a source data type of varchar(21)), which I'm trying to insert into a numeric(16,0) column, which obviously could cause issues.

So I made the accnum column varchar(21) as well in the physical table I created and it imports 100%. I also added an additional column called accnum2 and made it numeric(16,0).

After the data is imported, I proceeded to update accnum2 to the value of accnum. Lo and behold, it updates without an error, yet it wouldn't work with an insert into...select query.

I have to work with the data types provided. Any ideas how I can get around this?

Can you show us the query and table structure?

digital.aaron
– digital.aaron

2018-12-19 22:52:04 +00:00
Commented Dec 19, 2018 at 22:52 — digital.aaron
– digital.aaron, Commented Dec 19, 2018 at 22:52

marc_s · Accepted Answer · 2018-12-20 05:12:49Z

1

Can you try to use conversion in your insert statement like this:

SELECT [accnum] = CASE ISNUMERIC(accnum) 
                     WHEN 0 THEN NULL 
                     ELSE CAST(accnum AS NUMERIC(16, 0)) 
                  END

edited Dec 20, 2018 at 5:12

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Dec 19, 2018 at 22:56

Slava Murygin

1,9551 gold badge11 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thomas Over a year ago

That works. The weird thing is that the code I had before has remained unchanged for at least 4 years and this only occurred now. I extracted all the new bank account numbers and they look fine (did regex checks etc to check for spaces or non-numeric characters etc.) It's still a mystery why this occurred all of a sudden...

Slava Murygin Over a year ago

Be careful with that code. You might have come up with NULLs where a person might see a number. Something like "123456789-1" will produce NULL.

Collectives™ on Stack Overflow

SQL Server: Error converting data type varchar to numeric (Strange Behaviour)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related