Issues Converting String to Decimal

[thermidor]

Hi All:

I have varchar values like '01234560' that I need to convert to decimals like 123.456. The length of the varchar values change, so I'd like to be able to do something like:

DECLARE @var1 varchar(8) , @var2 int
SET @var1 = SUBSTRING('0123456', 1, 4) + '.' + SUBSTRING('0123456', 5, 3)
SET @var2 = LEN(@var1)
SELECT convert(decimal(@var2, 4),@var1)

This fails: SELECT convert(decimal(@var2[/color red], 4),@var1)
However, this succeeds: SELECT convert(decimal(8[/color red], 4),@var1)

Any ideas why this won't work?

TIA,
Sven

 

[ousoonerjoe]

I think DECIMAL wants an absolute. It is classified as an "Exact Numeric".

Why are you wanting a varying data type declaration?

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[gmmastros]

It looks to me like you want 4 digits of precision after the decimal point. When you use the decimal data type, you specify 2 numbers. The first represents the total number of digits, and the second is the number of digits after the decimal point.

So... Decimal(5,3) can store numbers in the range of -99.999 to 99.999

For your situation, I would just go with a relatively large number with a scale of 4, so....

Decimal(20,4). This would allow for 16 digits before the decimal place.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

yeah... What George said.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------