Padding with Zeros's in SQL Query

Transact-SQL
1

Can someone help me how to get this query to format with 2 decimal place. Thank you in advance
select PAY_RATE,
PAD_PAY_RATE = RIGHT(REPLICATE('0',13) + CAST(PAY_RATE AS varchar(13)),13)
from EMPLOYEE
Return output with 4 decimal place. I ONLY want 2 decimal place.

Thank you.

2

Cast ( column as decimal(15,2) )
First

Then varchar it

3

can you give me example please. thanks

4

what data type is column PAY_RATE in EMPLOYEE table?

5

hi

please see example below
notice it rounds up 7891 to 79
if you dont want round up also possible

Also another way is there
from the right most character
remove 2 characters

please let me know if you want to see this also
:slight_smile:
:slight_smile:

drop create data 
use tempdb 
go 

drop table EMPLOYEE
go 

create table EMPLOYEE
(
pay_rate varchar(100)
)
go 

insert into EMPLOYEE select '3456.7891'
go
SQL... 
SELECT pay_rate, 
       PAD_PAY_RATE = RIGHT(Replicate('0', 13) 
                            + Cast(pay_rate AS VARCHAR(13)), 13), 
       Cast(Cast(RIGHT(Replicate('0', 13) 
                       + Cast(pay_rate AS VARCHAR(13)), 13) AS DECIMAL(15, 2)) 
            AS 
            VARCHAR) 
FROM   employee
Results

image
image.png1120×203 4.27 KB

6

Harish,

you may have overthought the last column

use tempdb 
go 

drop table EMPLOYEE
go 

create table EMPLOYEE
(
pay_rate varchar(100)
)
go 

insert into EMPLOYEE 
values
( '3456.7891'),
('123.4'),
('678')

go

SELECT pay_rate, 
       PAD_PAY_RATE = RIGHT(Replicate('0', 13) 
                            + Cast(pay_rate AS VARCHAR(13)), 13), 
       Cast(Cast(RIGHT(Replicate('0', 13) 
                       + Cast(pay_rate AS VARCHAR(13)), 13) AS DECIMAL(15, 2)) 
            AS 
            VARCHAR) 

		, cast(cast(pay_rate as decimal(10,2)) as varchar(10))
FROM   employee
7

Thanks Mike

Got you

8

thank you very much