Skip to content

T-SQL Tuesday

Code I can’t live without

This is my contribution to the T-SQL-Tuesday #104 TSQL Tuesday

“Left” padding of numbers

One of the things I wouldn’t miss in my SQL Server environment, is my dbo.padl function. Like the lpad function on oracle, this function is padding “0”‘s to the left for a given number.

Example

SELECT dbo.PADL(12, 3)

Result: 012

The code is derived from several forum or blog posts from the early 2000er days. There may be exists more efficient ways to do the same, but this worked for since years.

Code

CREATE FUNCTION [dbo].[PADL]
(
  @nValue DECIMAL
, @nLength TINYINT
)
RETURNS VARCHAR(MAX)
AS
BEGIN

    DECLARE @cValue VARCHAR(20)
    SET @cValue = CONVERT(VARCHAR(50), @nValue)

    SET @cValue = REPLICATE('0', @nLength - LEN(LTRIM(RTRIM(CONVERT( VARCHAR(MAX), @nValue))))) + LTRIM(RTRIM(CONVERT( VARCHAR(MAX), @nValue)))

RETURN @cValue
END

Do I really need it?

I often discuss this code and several other helper functions for string formatting with colleagues. We never found a good replacement for this functionality, so it stays in my tool belt.

May be Microsoft will implement a similar function in SQL Server like Oracle has for years. You can upvote my feature request

Published inUncategorized

One Comment

Comments are closed, but trackbacks and pingbacks are open.

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