Ever wanted a PadLeft for SQL server
Here’s the next best thing, a scalar function we wrote and implemented into all our databases
Enjoy
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Andre Pageot | Michael Grabsky -- Create date: 06/02/2014 -- Description: Pads a string out -- USAGE: SELECT dbo.fnPadLeft('123456',9,'0') -- returns: 000123456 -- ============================================= CREATE FUNCTION [dbo].[fnPadLeft] ( -- Parameters taken from the .NET implementation of PadLeft @StringToPad varchar(250) ,@totalWidth int ,@paddingChar varchar(1) ) RETURNS varchar(250) AS BEGIN /* DECLARE @StringToPad varchar(250) ,@totalWidth int ,@paddingChar varchar(1) SELECT @StringToPad = '123456',@totalWidth = 9,@paddingChar='0' */ DECLARE @returnString varchar(5) DECLARE @PadString varchar(max) SET @PadString ='' WHILE LEN(@Padstring) < @totalWidth BEGIN SELECT @PadString = @PadString + @paddingChar END --SELECT RIGHT(@PadString + RTRIM(@StringToPad), @totalWidth) RETURN RIGHT(@PadString + RTRIM(@StringToPad), @totalWidth) END |