If you are working a lot with numbers in SQL Server or other database, sometimes it’s necessary to cast a text to numeric or vachar to int or varchar to numeric datatype; to be successful, the precision of the destination variable must match the text for all scenarios.
This came up in a recent situation where a Bank Number needs to be manipulated through an algorithm to determine certain things for the business. Don’t ask why, it’s a mess. But a problem existed and we were not able to find many solutions on the Internet, so I found out what the issue was and hopefully it will resolve a lot of issues with other people. This example may or may not match your exact situation, but might help get you where you need to be.
What’s interesting is that the BOL forSQL Server even tell you this in the Convert/Cast help.
SQL Server returns an error message when nonnumeric char, nchar, varchar, or nvarchar data is converted to int, float, numeric, or decimal. SQL Server also returns an error when an empty string (” “) is converted to numeric or decimal.
So, that throws a wrench into the understanding. Here is some code that recreates the error me and one that is correct. Note that when you are working with Numeric, the (p)recision is eaten up by any (s)cale you supply.
- Numeric(30,0) = 30 precision length, and 0 scale length; left of the decimal
- Numeric(30,5) = 25 precision length, and 5 scale length; left of the decimal
- Numeric(25,8) = 17 precision length, and 8 scale length; left of the decimal
In our case, we are taking a 26 varchar and converting to numeric without any scale. Thus we need at least any of the following if scale is used, since we don’t use sclae, the best choice for our scenario is the first one where numeric.p = len(varchar) = 26.
- Numeric(26,0) = 26 chars
- Numeric(27,1) = 26 chars
- Numeric(28,2) = 26 chars
So, as it states in BOL for “decimal and numeric” that “the maximum storage sizes vary, based on the precision”. The numeric.p – numeric.s > len(varchar).
The final result.
DECLARE
@BAN_Orig_Text varchar(50)
, @BAN_Mod_Text varchar(50)
, @BAN_Num numeric(26,0)
, @Len int
--- To pick the right (p,s) value above...
--- 26 = 26-0, 27-1, 28-2, 29-3, 30-4, 31-5, 32-6, 33-7, 34-8, 35-9
-- Set the variables
SELECT
@BAN_Orig_Text = '00191011528700000014881201'
, @Len = LEN(@BAN_Orig_Text)
-- Move the first 4 digitis to the right of the number. Just because.
, @BAN_Mod_Text = Right(@BAN_Orig_Text, @Len - 4) + Left(@BAN_Orig_Text, 4)
-- The work of converting...
SELECT @BAN_Num = CONVERT(numeric(26,0),LTrim(@BAN_Mod_Text))
-- Display the results
SELECT [BAN_Orig] = @BAN_Orig_Text, [BAN_Num] = @BAN_Num