SQL Server - Convert decimal to string with padding - implied decimal

Question

I have a need to format numbers with padded zeros left and right. The number's precision is decimal (9,5). I am converting to a character string and need it to be a length of 4. I am using SQL server 2008.

Here are some examples:

If 3.0 then I need 0003
If 30.0 then I need 0030
If 112.8 then I need 1128
If 120.0 then I need 1200

and so on.

I have tried multiple format functions like Right, Left, Replace, etc. But no combinations seem to get me the correct format.

Example below:

Right('00' + Replace(SUBSTRING(CAST(My_Table.My_Field as varchar),1,4),'.',''),4)

It works fine for the number 36.0 but this gets me 0212 for the number 212.0. I need it to be 2120

At what number do you start including decimal places? What is the “correct format” for 99.9? — Dour High Arch
– Dour High Arch, Commented Aug 14, 2013 at 22:56
Why does 3.0 = 0003, 30.0 = 0030, and 120.0 = 1200? Shouldn't 120.0 = 0120 to fit the pattern? — tigeravatar
– tigeravatar, Commented Aug 15, 2013 at 2:29

msi77 · Accepted Answer · 2013-08-15 13:46:50Z

2

Another one

right('000'+replace(value,'.',''),4)

answered Aug 15, 2013 at 13:46

msi77

1,6321 gold badge11 silver badges10 bronze badges

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

Comments

Gaston Flores · Accepted Answer · 2013-08-15 02:14:20Z

1

Maybe the following sql code will be useful:

SELECT
CASE WHEN  NUMBER < 100
  THEN RIGHT('0000' + CAST(CAST(NUMBER AS INT) AS VARCHAR),4)
  ELSE RIGHT(SUBSTRING(CAST(NUMBER AS VARCHAR),1,CHARINDEX('.',CAST(NUMBER AS VARCHAR))-1),4) + LEFT(PARSENAME(NUMBER,1),1)
END RESULT
FROM TEST

You can try this here sqlfiddle.

Note: NUMBER is your field, and TEST is your TABLE.

answered Aug 15, 2013 at 2:14

Gaston Flores

2,4773 gold badges25 silver badges42 bronze badges

Comments

tigeravatar · Accepted Answer · 2013-08-15 02:45:08Z

1

If 3.0 is supposed to be 0030 and 30.0 is supposed to be 0300, which would make sense for if 120.0 is supposed to be 1200, then this select statement should work:

SELECT SUBSTR('0000'||REPLACE(TableName.NumberColumn,'.',''),-4,4) AS Output FROM TableName;

answered Aug 15, 2013 at 2:45

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

Comments

Devart · Accepted Answer · 2013-08-15 05:27:29Z

0

Try this one -

DECLARE @temp TABLE (Value DECIMAL(4,1))

INSERT INTO @temp (Value)
VALUES (3.0),(30.0), (112.8),(120.0)

SELECT RIGHT('000' + CAST(CAST(Value * 10 AS INT) AS VARCHAR(4)), 4) 
FROM @temp

Output -

answered Aug 15, 2013 at 5:27

Devart

120k23 gold badges172 silver badges189 bronze badges

Collectives™ on Stack Overflow

SQL Server - Convert decimal to string with padding - implied decimal

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related