CODEWITHSUNDEEP
sqlsql-server
Saaharjit Singh
Saaharjit Singh

Reputation: 125

How to format a column in SQL Server?

I have table with column named amount. I want to display it in such a way that there are always 10 digits. The amount should be right justified with the last two digits displaying cents. If there are no cents then the last two digits should be 00 for eg:-

Name   Amount   New Column
---------------------------
aa      287.66  0000028766
bb       66     0000006600
cc     2475.1   0000247510
dd      214     0000021400

Upvotes: 1

Views: 13581

Answers (4)

Von Abanes
Von Abanes

Reputation: 716

As long Amount is string and has a blank space.

   DECLARE @T TABLE
            (
              NAME VARCHAR(10) ,
              Amount NVARCHAR(254)
            )

        INSERT  INTO @T
                ( NAME, Amount )
        VALUES  ( 'aa', N'     287.66' ),
                ( 'bb', N'      66  ' ),
                ( 'cc', N'    2475.1 ' ),
                ( 'dd', N'     214  ' );
        WITH    CTE
                  AS ( SELECT   name ,
                                amount ,
                                IIF(ISNUMERIC(REPLACE(Amount, ' ', '0')) = 1, CONVERT(INT, REPLACE(REPLACE(Amount,
                                                          '.', ''), ' ',
                                                          '0')), 0) AS NC
                       FROM     @T
                     )
            SELECT  name ,
                    amount ,
                    FORMAT(NC, '0000000000') [New COLUMN]
            FROM    CTE

Result

    name       amount        [New COLUMN]  
    ---------- -----------   --------------
    aa              287.66   0000028766
    bb               66      0000006600
    cc             2475.1    0000247510
    dd               214     0000021400

    (4 row(s) affected)

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 452957

You can use

Format(CAST(Amount * 100 as INT), 'D10')

Or

Format(Amount * 100 , '0000000000')

As long as you are on at least 2012.

Upvotes: 3

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

you can use right function in sql server as below:

select name, Amount, NewColumn = right('0000000000'+ convert(varchar(10),convert(int,amount*100)),10) from yourtable

Upvotes: 2

McNets
McNets

Reputation: 10807

You can use [REPLICATE()][1] function for this purpose.

Just to avoid decimal point you should convert to integer before to cast is as a varchar.

DECLARE @MyVal decimal(18,2) = 1252.23;
SELECT REPLICATE('0', 10 - LEN(CAST(CAST(@MyVal * 100 AS INT) as VARCHAR(10)))) 
       + CAST(CAST(@MyVal * 100 AS INT) as VARCHAR(10))
GO

| (No column name) |
| :--------------- |
| 0000125223       |

dbfiddle here

Upvotes: 1

Related Questions