Click here to Skip to main content
Click here to Skip to main content

SQL Server StringPadding (String function)

, 24 Apr 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
Custom StringPadding functions for SQL Server

Introduction

SQL Server 2008/2012 provides many string handling functions. You can find sub string, character or pattern index, left or right functions. Still you need to put some workaround if you need to pad a replicated character to the left or right side of a string. .NET and many other languages have built-in string padding functions.

Using the code

I created these function for String padding. 

  • StringPadLeft - Pads a string with leading characters to a specified total length.
  • StringPadRight - Pads a string with trailing characters to a specified total length.

Usage

Declare @CUSTOMER_ID as varchar(10)
Set @CUSTOMER_ID = dbo.StringPadLeft('46',10,'0')
Print @CUSTOMER_ID

StringPadLeft function

CREATE FUNCTION StringPadLeft 
 ( @String as Varchar(max),
   @Length as int,
   @Char as char(1)
 )
RETURNS Varchar(max)
AS
BEGIN

    Declare @PaddedString as Varchar(max)

    -- Return same string if @Length is smaller or equal to the length of @String value
    If(Len(@String) >= @Length )
            RETURN @String

    -- Create PaddedString
    Set @PaddedString = Replicate(@Char, @Length- Len(@String)) + @String

    -- Return the PaddedString
    RETURN @PaddedString

END

StringPadRight function

 
CREATE FUNCTION StringPadRight
 ( @String as Varchar(max),
   @Length as int,
   @Char as char(1)
 )
RETURNS Varchar(max)
AS
BEGIN

  Declare @PaddedString as Varchar(max)

    -- Return same string if @Length is smaller or equal to the length of @String value
    If(Len(@String) >= @Length )
            RETURN @String

    -- Create PaddedString
    Set @PaddedString = @String + Replicate(@Char, @Length- Len(@String))

    -- Return the PaddedString
    RETURN @PaddedString

END

Note : These functions will return same string if @Length is smaller or equal to the @String value.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Surender Singh (CodeToastDev)
Software Developer
United States United States
MCSD, MCTS, MCP
Personal Blog : http://www.codetoastdev.com/
Follow on   Google+

Comments and Discussions

 
GeneralVery nice article Pinmembergaurav23259-May-13 9:07 
GeneralRe: Very nice article PinprofessionalSurender Singh (CodeToast)10-May-13 8:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141220.1 | Last Updated 24 Apr 2013
Article Copyright 2013 by Surender Singh (CodeToastDev)
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid