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'
Free SQL Comparison tools
Trusted by thousands of users
Download your copy now
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
select dbo.udfLeftSQLPadding('123',10,'0')
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
select dbo.udfRightSQLPadding('123',10,'0')
select dbo.udfRightSQLPadding('123',10,'*')
|