Monday, May 9, 2011

Microsoft SQL Server T-SQL: String Left/Right Padding


Microsoft SQL Server 2005 T-SQL does not have left/right string padding function, to use we need to write our own Microsoft SQL  function. The following code example shows how to create a function for Left string padding

[T-SQL Code Snippet]

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
Go
CREATE FUNCTION [dbo].[lPad]
(
 --Input String to Pad
  @in_str AS VARCHAR(MAX)
 --Character used for padding an input string
 ,@in_padd_char AS VARCHAR(1)
 --Number of Chracter in resulting string, equals to the number of chracters in input string plus additonal padding chracters
 ,@in_total_width AS INT
)
RETURNS VARCHAR(MAX) WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @out_padded_str AS VARCHAR(MAX)
SET @out_padded_str = ISNULL( REPLICATE(@in_padd_char, @in_total_width - len(@in_str) ), '') + @in_str
RETURN @out_padded_str
END

Usage Example 

SELECT DBO.lPad ('12345', '0', 10)
----------------------------------------------------------
Result: 0000012345


2 comments: