SQL Server Zero Pad Left

Facebooktwittergoogle_plusredditpinterestlinkedinmail

In a recent project, I had the task of padding a number with zeros. SQL Server doesn’t have a built in Zero Pad Left function. SQL Server does have a Right function. You can use this to create a zero padded left string.

In the code above, I am take 10 zeros, appending a text value to it, then taking the right 10 characters. The end result is the same as padding the left with zeros.

 
SQL Server Zero Pad Left Function
You can take this a step further and create a scalar function out of it.

1 thought on “SQL Server Zero Pad Left

  1. Sql Server has since version 2012 the Format() option
    ie: SELECT FORMAT(id, ‘0000’) as myId FROM myTable;
    if id = 1 you will get 0001 returned

Leave a Comment

CommentLuv badge