Concerned with replicate fn in sql

Question

hi guys, I am using following query

create table  #Attendence (id int identity(1,1),det varchar(2000))
insert into #Attendence (det )
select  --convert(char(10),@Date,3) +REPLICATE(' ', 20 - LEN(convert(char(10),@Date,3)))+
        staff.StaffNAme +REPLICATE(' ', 20 - LEN(staff.StaffNAme ))+
        case Att.FN when 1 then 'Present'
                else 'Absent'
        end  +REPLICATE(' ', 20 - LEN(case Att.FN when 1 then 'Present'
                else 'Absent'
        end ))+
        case Att.AN when 1 then 'Present'
                else 'Absent'
        end +REPLICATE(' ', 20 - LEN(case Att.AN when 1 then 'Present'
                else 'Absent'
        end  ))
from  Staff_Details staff
      inner join
        STAFF_Attendance att
            on staff.staffid=att.staffId
Select * from   #Attendence

The problem is in the resultset as the length of STaffName increases, the values in the right of staffname is moving rightward.Can anybody help to make each column fixed length?

Answer 1

I guess Staffname has trailing spaces. These will be ignored by LEN.

Try this to force a length of 20 chars exactly:

LEFT(staff.StaffNAme + '                    ', 20)

...instead of...

staff.StaffNAme + REPLICATE(' ', 20 - LEN(staff.StaffNAme))