SQL Add Leading Zeros

The following post provides a few examples of how to add leading zeros to CHAR, VARCHAR and INT data type field values.  I have to use these techniques quite often in my data world, so I figured it was a good post to share with others struggling with this task.  Unfortunately, these techniques often result from working with flawed data, but that is often the norm rather than the exception.  I hope you find these useful.  If you have additional techniques, I encourage you to add them to the Post Comments section.

All examples below format a given value to 5 characters with leading zeros.

All examples below have been tested in SQL 2014.

All examples below (other than CONCAT and FORMAT) have been tested in SQL 2008 R2.

SQL Add Leading Zeros – Example 1 – CHAR data type

Results:

SQL Add Leading Zeros

 

SQL Add Leading Zeros – Example 2 – VARCHAR data type

Results:

SQL Add Leading Zeros

 

SQL Add Leading Zeros – Example 3 – INT data type

Results:

SQL Add Leading Zeros

 

 

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

 

 

Leave a Comment