SQL Tutorial - SQL Pad Leading Zeros
In order to sql pad leading zeros, sql developers use sql string concatenation and sql string functions LEFT and RIGHT.
LEFT and RIGHT sql string functions enable sql developers to take left part of a string variable or column, or extract right part of a string.
A parameter enables the extracted string to be in a definite length.
Here in this t-sql tutorial, you will find methods for sql padding zeros in your t-sql codes.
Then I will give some more information about SQL Server SQL String functions like RIGHT, LEFT and REPLICATE here.
The last section of the sql tutorial on padding with zeros or with any other characters will display t-sql codes of two user defined sql zero padding functions.
These functions are udfLeftSQLPadding and udfRightSQLPadding for sql padding from left or start of the string and sql padding from the right or end of the string.
DECLARE @sqlVariable nvarchar(15)
SET @sqlVariable = '1234567890'
SELECT
@sqlVariable,
REPLICATE('0', 15),
'000000000000000' + @sqlVariable,
RIGHT('000000000000000' + @sqlVariable, 13)
SELECT STUFF(@sqlVariable, 1, 0, REPLICATE('0', 15 - LEN(@sqlVariable)))
SELECT RIGHT(REPLICATE('0', 15) + LTRIM(RTRIM(@sqlVariable)), 15)
SELECT STR(@sqlVariable,15), REPLACE(STR(@sqlVariable,15), SPACE(1), '0')
Now let's go deeper with each sql function used for sql paddding zeros.
SQL string RIGHT function has the following syntax :
Right( string_expression, length )
The SQL Right function returns characters beginning from the end of the string expression in "length" argument value characters long.
Just like the Right function, sql string function Left() has the same syntax.
Left( string_expression, length )
On the other hand, just the opposite of Right function, SQL Left function returns length argument value characters long part of the string expression beginning from the start.
Here is a sql string function example using Right function and Left function.
SELECT
RIGHT('SQL Right Function', 8), -- returns 'Function'
LEFT('SQL Right Function', 3), -- returns 'SQL'
Another sql string function which is used to sql pad leading zeros is the SQL Replicate string function.
The string Replicate function has the following t-sql syntax :
Replicate( character_expression, integer )
Replicate sql function returns a new string value which has i times concatenated value of the character expression.
Here is a sql Replicate function example:
SELECT REPLICATE ('0', 5), -- returns '00000'
CREATE FUNCTION udfLeftSQLPadding (
@String NVARCHAR(MAX),
@Length INT,
@PaddingChar CHAR(1) = '0'
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN (
SELECT RIGHT(Replicate(@PaddingChar, @Length) + @String, @Length)
)
END
GO
Here how we can use udfLeftSQLPadding user defined function for zero padding in SQL codes.
select dbo.udfLeftSQLPadding('123',10,'0') as [zero padding]
union
select dbo.udfLeftSQLPadding('12345',10,'0')
union
select dbo.udfLeftSQLPadding('123456789',10,'0')
The output of the above sample SQL function will be as follows
SQL programmers can create an other user defined SQL function for right padding or adding characters to the tail of the input value to fix the output length.
CREATE FUNCTION udfRightSQLPadding (
@String NVARCHAR(MAX),
@Length INT,
@PaddingChar CHAR(1) = '0'
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN (
SELECT LEFT(@String + Replicate(@PaddingChar, @Length), @Length)
)
END
GO
Following SQL Select statement shows what will be the output of udfRightSQLPadding function.
select dbo.udfRightSQLPadding('123',10,'0') as [right zero padding]
union
select dbo.udfRightSQLPadding('123',10,'*')
union
select dbo.udfRightSQLPadding('1234567',10,'*')
As seen below, the output length is 10 as passed to the SQL function and missing characters are added to the end of the input expression.
