Struggling with formatting decimal numbers and padding them with zeros to be saved in the char fields

Question

Struggling with formatting decimal numbers and padding them with zeros to be saved in the char fields

VoldemarG

Hall of Fame

Points: 3883
More actions
November 12, 2018 at 8:35 am

#368372

I am preparing data for being output to text file via ssis in a certain format required by the receiving system.
I need to convert 2 columns in the SQL Server into a specific format that is not usual (the receiving system is an old cobol program...).
1) numbers like this: 4.5 into '0045' or 98.3 into '0983' by both formatting and padding with leading zeros to the length of 4 digits. Trying to convert decimal to char (4) (as shown on the top part of the attached screenshot-1). ENTRY% column needs to be in the same format as entrypercent column on the bottom of the screenshot.
2) screenshot-2 shows how i need to reformat AvgDailyMembers column to. It is Decimal (13,4). For example, such number as 38 should be '0000000380000' (no decimal points in target format but scale of 4 should be represented by zeros). A number like 56.78 will be '0000000567800').
It looks like a headache, i have tried different ways ...
Datatypes of both ENTRY% column and AvgDailyMembers is Char. (char(6) and char (13)
I appreciate your help with this.
Likes to play Chess

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

VoldemarG Hall of Fame Points: 3883 More actions · Answer 1

The best I have done so far is adding leading zeros to entrynumber column (INT) (a different input format than in my post/quesiton).
update MobilityRates Set entrynumber = Replicate ( '0', 6-len(convert(varchar(6), entrynumber)) ) + convert(varchar(6), entrynumber)

but that was INT not decimal

Likes to play Chess

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 2

--DECLARE @myDecimal as decimal(4,1) = 4.5 --DECLARE @myDecimal as decimal(13,4) = 38 DECLARE @myDecimal as decimal(13,4) = 56.78 SELECT RIGHT('00000000000000000' + REPLACE(CONVERT(varchar,@myDecimal),'.',''),13)

VoldemarG Hall of Fame Points: 3883 More actions · Answer 3

Jonathan AC Roberts - Monday, November 12, 2018 9:24 AM
--DECLARE @myDecimal as decimal(4,1) = 4.5 --DECLARE @myDecimal as decimal(13,4) = 38 DECLARE @myDecimal as decimal(13,4) = 56.78 SELECT RIGHT('00000000000000000' + REPLACE(CONVERT(varchar,@myDecimal),'.',''),13)

THANKS! the second and third numbers convert/format correctly (38 and 56.78). I also tested with 56.1234, 56.123 and all work correctly except of the first one in your example:

DECLARE @myDecimal as decimal(4,1) = 4.5
SELECT RIGHT('00000000000000000' + REPLACE(CONVERT(varchar,@myDecimal),'.',''),13)

with numbers of precision=1 and scale = 1 (like in 4.5) the result comes out: 0000000000045
while it should be 000000045000

Likes to play Chess

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 4

I think you need to add another convert in the SQL.SELECT RIGHT('00000000000000000' + REPLACE(CONVERT(varchar,CONVERT(decimal(13,4),@myDecimal)),'.',''),13)

VoldemarG Hall of Fame Points: 3883 More actions · Answer 5

Jonathan AC Roberts - Monday, November 12, 2018 9:41 AM
I think you need to add another convert in the SQL.SELECT RIGHT('00000000000000000' + REPLACE(CONVERT(varchar,CONVERT(decimal(13,4),@myDecimal)),'.',''),13)

YES! works. thanks !

Likes to play Chess

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

I prefer using arithmetic over string expressions that require allocating and garbage collecting extra strings for every intermediate step:

DECLARE @dec FLOAT;
SET @dec = 94.3;
PRINT RIGHT(CAST(@dec * 10 AS INT) + 10000, 4);

SET @dec = 38;
PRINT RIGHT(CAST(@dec * 10000 AS BIGINT) + 10000000000000, 13)
SET @dec = 56.78;
PRINT RIGHT(CAST(@dec * 10000 AS BIGINT) + 10000000000000, 13)
0943
0000000380000
0000000567800

Sergiy SSC Guru Points: 110208 More actions · Answer 7

select replace ( replace( STR(38, 14,4), '.', ''), ' ', '0') select replace ( replace( STR(56.78, 14,4), '.', ''), ' ', '0')

_____________
Code for TallyGenerator