Skip to content

Padding of strings

Preamble

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.

 

Introduction

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;

PADD
—-
0010

 

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)
AS
BEGIN

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

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

Summary

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 *