Left Padding(LPAD) using REPLICATE

Saturday, 19 September 2009

Here's a SQL UDF to do the left padding using REPLICATE.

create function [dbo].[leftpad]

(

@csrting nvarchar(4000)

, @nlen smallint

, @cpadcharacter nvarchar(4000) = ' '

)

returns nvarchar(4000)

as

begin

declare @nleftlen smallint, @nrightlen smallint

declare @length smallint, @lenpadcharacter smallint

select @length = datalength(@csrting)/(case sql_variant_property(@csrting,'basetype') when 'nvarchar' then 2 else 1 end)

select @lenpadcharacter = datalength(@cpadcharacter)/(case sql_variant_property(@cpadcharacter,'basetype') when 'nvarchar' then 2 else 1 end) -- for unicode

if @length >= @nlen

set @csrting = left(@csrting, @nlen)

else

begin

set @nleftlen = @nlen - @length

set @csrting = left(replicate(@cpadcharacter, ceiling(@nleftlen/@lenpadcharacter) + 2), @nleftlen)+ @csrting

end

return (@csrting)

end