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

Update: 18.01.2018
Due to the move of connect to a user voice base plattform the item has also been moved New feedback plattform

Published inUncategorized

Be First to Comment

Leave a Reply

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

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.