SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



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 zero padding function udfLeftSQLPadding

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.

SQL zero padding function sample for right-padding






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems