Skip to content

Padding of strings


With this post I will start blogging about SQL Server, Powershell & C#. Starting with a T-SQL Tuesday blog post, which is hosted this month by Brent Ozar.



A long time ago I filed a connect item to request new funtions for padding a value with a defined value. I used and still need such a feature to prepare data for export data to system like MVS.

Example from Oracle with the left padding function

select lpad(10, 4, ‘0’) padd from dual;



Padding by T-SQL

Padding via T-SQL I realized via a function like this:


CREATE FUNCTION [dbo].[test_TSQL_LPAD_REPLICATE] (@nWert as int, @nLaenge as tinyint )
RETURNS varchar(20)

	RETURN(replicate('0', @nLaenge - len(cast(@nWert as varchar(20)))) + cast(@nWert as varchar(20)))

Running this function over  table with about 1.3m rows takes 31 sec.

Padding by SQLCLR

Padding via SQLCLR I relized with a database project with the following C# code:

using System.Data.SqlTypes;

public partial class UserDefinedFunctions
    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true)]
    public static SqlString clr_LPAD(SqlInt32 myValue, SqlInt32 length)
        // Put your code here
        return myValue.ToString().PadLeft((System.Int32)length, '0');

May there ist a better way in C# to implement this, but this just works. Smile Running the same 1.3m rows takes 35 sec. So there is no difference compared to the T-SQL implementation


At the end of the day, I want a standard function without implementing this common helper functionality at every database. Additionally I assume a native implementation of left or right padding will perform much better as my custom functions.

So, if you agree with me, please vote for my connect item #728597

Published inUncategorized

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *