Display 2 digit decimal with leading zeros

Transact-SQL
1

How to display 2 digit decimal with 20 leading zeros:

Example:

154.76 to be displayed as: 00000001547600000000
80.08 to be displayed as : 00000000800800000000
2.34 to be displayed as 00000000023400000000

2

Try:

DECLARE @tbl table(Num numeric(20,10));
INSERT @tbl
      ( Num )
   VALUES
      ( 154.76 )
   ,  (  80.08 )
   ,  (   2.34 );
SELECT 
   Right(Replicate('0', 20)+Cast(Num AS varchar(20)), 21) Num 
FROM @tbl;
3

Sorry - the decimal are still showing on the result.

Here is the entire SQL:

DECLARE @tbl table(Num decimal(16,2));
INSERT @tbl
( Num )
VALUES
( 154.76 )
, ( 80.08 )
, ( 2.34 );
SELECT
Right(Replicate('0', 20)+Cast(Num AS varchar(25)), 21) Num
FROM @tbl;

4

We are pretty close. All we need to do is remove the decimals.

DECLARE @tbl table(Num numeric(16,8));
INSERT @tbl
( Num )
VALUES
( 154.76 )
, ( 80.08 )
, ( 2.34 )
, ( 0.04 )

;
SELECT

Right(Replicate('0', 20)+Cast(Num AS varchar(20)), 21) Num
FROM @tbl;

5

Add a REPLACE like this:

SELECT
REPLACE(Right(Replicate('0', 20)+Cast(Num AS varchar(20)), 21) ,'.','')Num
FROM @tbl;
6

This works. Now I want to TRIM the last 8 characters from the RIGHT. Can you please help?

Result should look like this:

000000015476
000000008008
000000000234

7

I figured it out, Here is the SQL :

DECLARE @tbl table(Num decimal(16,2));
INSERT @tbl
( Num )
VALUES
( 154.76 )
, ( 80.08 )
, ( 2.34 );

SELECT
REPLACE(Right(Replicate('0', 12)+Cast(Num AS varchar(12)), 13) ,'.','')Num
FROM @tbl;

8 
SELECT num, REPLACE(STR(num * 100, 12), ' ', '0') AS new_num
FROM @tbl
9

This works too also