Varchar to decimal(15, 12) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Varchar to decimal(15, 12)

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-11-30 : 06:43:25

Hi all,

I need to do some data conversion in regards to a bulk insert and I have a several columns in the flat file which are defined with up to 12 digits after the comma, but the comma is not present in the file. So what I have is something like this:

DECLARE 
  @Row varchar(200),
  @Start int,
  @Length int,
  @Decimals int,
  @Field1 varchar(15)
  
SET @Row = '0000135400000000130NO01NOK0010540000100072010100007300010000'
SET @Start = 32
SET @Length = 15
SET @Decimals = 12
SET @Field1 = SUBSTRING(@Row, @Start, @Length)

SELECT @Field1

My question: How can I convert/transform @Field1 dynamically so that I can insert it in to a DECIMAL(15, 12) (or any other scale/precision) column in a table?

I tried using @Field1 / POWER(10, @Decimals) but that only works for 9 or less decimals...please let me know if I need to exlain further.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-30 : 07:11:11

Am I misinterpreting something?

DECLARE 
  @Row varchar(200),
  @Start int,
  @Length int,
  @Decimals int,
  @Field1 varchar(15),
  @result decimal(15, 12)
  
SET @Row = '0000135400000000130NO01NOK0010540000100072010100007300010000'
SET @Start = 32
SET @Length = 15
SET @Decimals = 12
SET @Field1 = SUBSTRING(@Row, @Start, @Length)

SELECT @Field1

SET @result = CAST(SUBSTRING(@Row, @Start, @Length - @Decimals) + '.' + SUBSTRING(@Row, @Start + @Length - @Decimals, @Decimals) AS decimal(15, 12))

SELECT @result

Peter Larsson
Helsingborg, Sweden

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-11-30 : 08:10:39

Umh...hehe...no you didn't misinterpret anything...it's spot on. Now if you'll excuse me I'll just go somewhere quiet until this noob-feeling passes...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources