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 *

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.

Close