SQL Server T-SQL LPAD & RPAD Functions (String Padding Equivalent to PadLeft & PadRight)
Here is my method for achieving left and right string padding in the Microsoft SQL Server T-SQL language. Unfortunately T-SQL does not offer functions like Oracle PL/SQL's LPAD() and RPAD() and C#'s PadLeft() and PadRight() functions. However, you can achieve the same thing using the T-SQL REPLICATE and LEN functions. Suppose you have a numeric column called Quantity and you need to return it as a string left-padded with 0's to exactly ten characters. You could do it this way:
SELECT REPLICATE('0', (10 - LEN(CAST(Quantity, VARCHAR)))) + CAST(Quantity, VARCHAR) AS PaddedQuantity
FROM TableXThe calls to the CAST function are based on the assumption that the value you padding is numeric. If Quantity were already an string you could do it like this:
SELECT REPLICATE('0', (10 - LEN(Quantity))) + Quantity AS PaddedQuantity
FROM TableXIn certain cases you might be concerned that that value you want to pad might be wider than your maximum number of characters. In that case you could use a CASE block to check the LEN of your input value to avoid passing a negative result to the second argument of the REPLICATE function. No need to worry about passing a 0 to REPLICATE, though: it will simply return an empty string, which is what you'd want since no padding would be necessary.
Update: I decided to go ahead and turn these into user defined functions. Here is a script for fnPadLeft:
IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[fnPadLeft]')
AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[fnPadLeft]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION fnPadLeft
(
@PadChar char(1),
@PadToLen int,
@BaseString varchar(100)
)
RETURNS varchar(1000)
AS
/* ****************************************************
Author: Daniel Read
Description:
Pads @BaseString to an exact length (@PadToLen) using the
specified character (@PadChar). Base string will not be
trimmed. Implicit type conversion should allow caller to
pass a numeric T-SQL value for @BaseString.
Unfortunately T-SQL string variables must be declared with an
explicit width, so I chose 100 for the base and 1000 for the
return. Feel free to adjust data types to suit your needs.
Keep in mind that if you don't assign an explicit width to
varchar it is the same as declaring varchar(1).
Revision History:
Date Name Description
---- ---- -----------
***************************************************** */
BEGIN
DECLARE @Padded varchar(1000)
DECLARE @BaseLen int
SET @BaseLen = LEN(@BaseString)
IF @BaseLen >= @PadToLen
BEGIN
SET @Padded = @BaseString
END
ELSE
BEGIN
SET @Padded = REPLICATE(@PadChar, @PadToLen - @BaseLen) + @BaseString
END
RETURN @Padded
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
And for fnPadRight:
IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[fnPadRight]')
AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[fnPadRight]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION fnPadRight
(
@PadChar char(1),
@PadToLen int,
@BaseString varchar(100)
)
RETURNS varchar(1000)
AS
/* ****************************************************
Author: Daniel Read
Description:
Pads @BaseString to an exact length (@PadToLen) using the
specified character (@PadChar). Base string will not be
trimmed. Implicit type conversion should allow caller to
pass a numeric T-SQL value for @BaseString.
Unfortunately T-SQL string variables must be declared with an
explicit width, so I chose 100 for the base and 1000 for the
return. Feel free to adjust data types to suit your needs.
Keep in mind that if you don't assign an explicit width to
varchar it is the same as declaring varchar(1).
Revision History:
Date Name Description
---- ---- -----------
**************************************************** */
BEGIN
DECLARE @Padded varchar(1000)
DECLARE @BaseLen int
SET @BaseLen = LEN(@BaseString)
IF @BaseLen >= @PadToLen
BEGIN
SET @Padded = @BaseString
END
ELSE
BEGIN
SET @Padded = @BaseString + REPLICATE(@PadChar, @PadToLen - @BaseLen)
END
RETURN @Padded
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Example usage:
SELECT dbo.fnPadLeft('X', 15, 100.25)
SELECT dbo.fnPadRight('X', 15, 'ABC')Comments welcome,
Dan
Thanks, Donna
I'll be doing a lot of SQL Server 2005 and SSIS work in the coming weeks, so I plan to continue blogging about it as I come up with things that might be helpful to other people. Have I recommended this excellent book yet for getting up to speed with SQL Server 2005? It's called Microsoft SQL Server 2005: Changing the Paradigm. It covers things from both the DBA and development perspectives, and has a very nice balance between giving high level strategic and tactical information about all the new stuff and getting into the weeds a little with some how-to and reference.
Dan
OK, but...
The 10 is hard coded. If it is a parameter or even defined as a symbolic constant PAD_MAX which is probably good practice in the surrounding programming language, it becomes a teensy hard to "stuff" into the SQL and the SQL's intent is obscured thereby. Which makes it a shame that T-SQL doesn't implement a pad() function. Failing this, it would be nice, I think, if T-SQL had the general ability to define user functions.
If quantity is negative, the code doesn't work. Here you need an absolute value function (is this provided in your SQL?) AND a SQL version of the VB Iif (or the C ?: operator, a fun little bit of strangeness) if you are going to stay in one SQL call. If the call goes to the server, you need to.
As it is you are going to get 00000-12345 and other strange outputs if this is an order application and there are "returns".
It's enough to drive a man either to drink, or else to writing his Own Goddamn SQL (OGSQL). That's been done, of course, but by somebunny else.
Dig the New UDFs
Your point about the "magic number" literal value of 10 is correct, Edward, but it was really just an example. My example was assuming a single SQL statement and I wanted to keep it simple, so I used the magic number. Unfortunately T-SQL does not support named constants explicitly, though you can approximate them with variables. Often, though, I solve the magic number problem in SQL with a single line comment above the line that uses the magic number to explain its meaning. If the code is within a T-SQL stored procedure, though, I'll create a pseudo-named-constant variable in place of the literal.
Also, in my post I suggested using branching logic rather than ABS() to handle the negative value possibility because after thinking about it I decided ABS() around the formula would assume too much about the intent of the caller by returning a string that is fewer characters than requested. My example does not contain the ABS() function because I wanted to leave that decision up to the coder to solve. I've added two user defined functions, and you can see there how I've decided to handle the negative problem. Someone else might prefer to use ABS() instead of the branching logic that I've implemented. Someone else might prefer to raise an error instead of just returning back the base string, as I have done in my UDFs.
Thanks for your interest,
Dan
Oh, sorry, there they are and they are kewl
I do like the formality of saying, here is the function and it merits a change record.
Centering is another alignment task where you divide the difference between the length of a string and the (usually larger) length of a container by 2 and offset the string in the area by the quotient. You use the integer floor of the quotient when there is an odd number because n monospace spaces to the left and n+1 look more natural.
I coded an align routine which depending on a parameter will left, right or center justify a string within another string early in my VB addiction and this legacy code survives in the utilities.DLL for Build Your Own .Net Language and Compiler.
In Rexx I added the ability to justify code with even boundaries on either side. Unfortunately this, and to a degree centering, is at best workable "typesetting" only in the wonderful world of monospace fonts like Courier New, where all characters are of identical width, and you can make documents that are "beautiful" only in typewriter terms.
Such a low skill is useful still in programming when writing error reports, logs, and ransom notes while REAL computerised typesetting is vastly more complex.
Indeed, during my early addiction to VB I contemplated writing a typesetting package and little language exclusively for monospace but soon enough realized that this would be a Plan Nine from Outer Space.
However, I have written code to place messages in asterisk boxes.
RPAD with spaces is assumed as null
I appreciate the effort in generating the function above. However, when trying to generate a fixed width file, one has to right pad alpha fields with spaces. Unfortunately, the spaces are treated like and are truncated when inserting into a field. Do you have a method to rpad spaces to an alpha field? I look forward to your feedback.
Caesar
Spaces - varchar issue?
Hmmmm...could your right-padded spaces be disappearing because you're storing the string in a varchar variable? I believe right-padded spaces in a varchar are automatically removed. If this is the case, you might try storing your values in char variables/columns while processing your file export. Or have I misunderstood your issue?
Hope that helps,
Dan
thanks
Excellent function. Worked like a charm.
LPAD and RPAD
the RPAD and LPAD functions in SQL Server are very clever and appear to work perfectly. This is for non-commercial work, but I have given you credit in the code. Thanks!
scott
Prairie Heart Institute
Anything wrong with my method
I needed an int converted into a varchar of 6 characters with leading zeros. Add 6 zeros to the left of the converted int, then just take the 6 characters on the right.
RIGHT('000000' + CAST(dbo.Company.ID AS VARCHAR), 6) AS ID
Is this terribly ineffecient?
Looks Like a Viable Solution
I can't say whether or not this is particularly inefficient, but it looks like it should work fine as long as your company ID stays bellow 1,000,000. I believe a SQL Server int (> 0) can be up to 9 characters (?). Beyond that, though, I can't see anything wrong with it on the surface, especially if it were generalized into it's own function that took an int argument and returned a string. If you use this technique inline, it's less communicative than a function call would be.
Assuming you removed the limit on the 6 characters and generalized it into a function, it would be interesting to compare the two solutions for performance. Thanks for contributing this alternate solution.
Dan
Length not issue for us
Our member numbers are all 6 characters, so I don't need to worry about anything larger. Infact our lowest numbers are still 3 digits so I could simply append 3 zeros to the leading edge.
I never considered creating a function, this is just a quick fix from our new database system to feed the web SQL box until we get the new system completely up and running.
Hard Coded Limits
To clarify Dan's comment, SQL Server ints can be up to 10 characters.
Also check out a related post, "Hard Coded Limits Considered Harmful," on my blog.
Edward
Also check out a related
Also check out a related post, "Hard Coded Limits Considered Harmful," on my blog.
Because there is an alternative to the hard coded values present in the functions? I don't see one...
Keep serving that SQL
Dan, I (for one) am appreciating your recent posts involving SQL Server. There's not a day that I don't go into Enterprise Manager (in the 2000 realm) and really need to wade into the 2005 waters.