LPAD and RPAD Functions in SQL

This way is very simple, but it doesn't protect against the source value being longer than the specified length and it doesn't account for trailing SPACEs in the source value.

DECLARE @W INTEGER -- Width for result
SET @W = 10

DECLARE @X NVARCHAR(MAX) -- Value to pad
SET @X = 'VALUE'

DECLARE @Y NVARCHAR(MAX) -- Pad character
SET @Y = '#'

DECLARE @Z NVARCHAR(MAX) -- Result
SET @Z = @X + REPLICATE(@Y,@W-LEN(@X)) -- Right pad

SET @Z = REPLICATE(@Y,@W-LEN(@X)) + @X -- Left pad