CODEWITHSUNDEEP
sqlsql-serverformatdecimal
Shweta Reddy
Shweta Reddy

Reputation: 371

Formatting decimal leading with zeros

I have a requirement which says, it should be rounded by two decimal places and should contain 8 digits as result.

   Output Example:
   1234567.88  to 12345678  -- As format is only for 8 digit by removing decimal
   123.45      to 00012345
   748.55137   to 00074855

For this I have done below way, in which FORMAT or LEFT is not working.

SELECT REPLACE(RIGHT('00000000'+LEFT(CAST(CAST( ROUND((((ISNULL(511.90,0)*(ISNULL(146.230,0)/100))),2) AS DECIMAL( 15,0)) AS VARCHAR( 15 )), 8 ), 8 ),'.','')
 Expected Output: 00074855

I have done LEFT, FORMAT(.., '00000.00') but nothing seems working here in CASE.

Thanks all for the inputs.

Working Sample: 

REPLACE(FORMAT(CAST(ROUND(((ISNULL(511.90,0) * (ISNULL(146.230,0)/100))),2) AS DECIMAL(10,2)),'000000.00'),'.','')

Upvotes: 0

Views: 562

Answers (4)

Zhorov
Zhorov

Reputation: 29943

Statement SELECT LEFT(FORMAT(Amount * 100, '00000000'), 8), using LEFT() and FORMAT() with custom formatting string, returns the expected results:

Statement:

SELECT 
   Amount, 
   LEFT(FORMAT(Amount * 100, '00000000'), 8) AS FormattedAmount
FROM (
   VALUES (1234567.88), (123.45), (748.55137)
) v (Amount)

Result:

Amount          FormattedAmount
1234567.88000   12345678
123.45000       00012345
748.55137       00074855

Upvotes: 1

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try this using REPLICATE (Transact-SQL) function.

Here 8 is the length you want of the text including padding 0.

declare @id as varchar(10) = replace('1234567.88', '.', '')
SELECT Convert(Varchar(8),CONCAT(REPLICATE('0', 8 - LEN(@id)), @id)) as AfterPadding

set @id = replace('123.45', '.', '')
SELECT Convert(Varchar(8),CONCAT(REPLICATE('0', 8 - LEN(@id)), @id)) as AfterPadding

Live db<>fiddle demo.

For your table, you need to apply like this.

Select 
     Convert(Varchar(8),
     CONCAT(REPLICATE('0', 8 - LEN(Replace(Cast(AMOUNT as Varchar(10)), '.', ''))), 
     Replace(Cast(AMOUNT as Varchar(10)), '.', ''))
     ) as AmountInText
from SampleDataTable

Live db<>fiddle demo.

Upvotes: 0

Alex
Alex

Reputation: 5157

Input number: 1234567.88 in decimal/numeric format

SELECT RIGHT( '00000000' + LEFT( CAST( CAST( 1234567.88 * 100 AS DECIMAL( 15, 0 )) AS VARCHAR( 15 )), 8 ), 8 )

Output: 12345678

How it works:

  1. CAST( 1234567.88 * 100 AS DECIMAL( 15, 0 )) - converting to a smaller precision automatically rounds the number (see Converting decimal and numeric data)
  2. CAST( ... VARCHAR( 15 )) - cast to string
  3. LEFT( ... , 8 ) - truncate number to 8 digits
  4. RIGHT( '00000000' + ... , 8 ) - add leading zeroes, then truncate keeping 8 rightmost characters

FYI: if you use decimal/numeric formats for your calculations then there is no need for "funny" number divisions and multiplications (/.05)*.05).

Upvotes: 0

ahmed abdelqader
ahmed abdelqader

Reputation: 3560

You can simply use LEFT for padding zeros , REPLACE for removing the dot and CONVERT for converting the value from string to number as next:-

SELECT LEFT('00000000'+ CONVERT(int,REPLACE('1234567.88', '.', '')),8) -- output: 12345678

SELECT Right('00000000'+ Convert(varchar(8), Convert(int,REPLACE('123.45', '.', ''))),8) -- output: 00012345

Upvotes: 0

Related Questions