The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

    • RT @nadineswagerman: Iemand mailde me dat een 13-jarig meisje mijn novelle had uitgekozen om te lezen/bespreken op school. Het boek bevat 9… 3 hours ago
    • RT @jilles_com: Afgelopen 10 dagen hebben we door NL gereisd, elke dag een nieuw hotel en op heel veel locaties gegeten. In 60% van de geva… 3 hours ago
    • RT @FTM_nl: Sywert van Lienden plaatste bewust een serie tweets om het ministerie onder druk te zetten om de omstreden mondkapjesdeal met h… 3 hours ago
    • RT @ArmsControlWonk: China just used a rocket to put a space plane in orbit and the space plane glided back to earth. Orbital bombardment i… 3 hours ago
    • RT @shossontwits: O jee. Nu dit weer. 4 hours ago
  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,482 other followers

Pad Numbers with Leading zeros (0s) or trailing spaces – Fixed Width Number Display (via: SQL Server Journey with SQL Authority)

Posted by jpluimers on 2012/09/12

Every once in a while I need something like this, and I always forget the best way to do it: pad numbers so they start with leading zeros so they appear fixed with.

The SQLAuthority phrases it:

There is no ready made function in SQL Server which offers this solution but we can quickly write up something very simple.

SQLUSA has some more ways to do it, and all basically come down to this:

  1. Convert your number to VARCHAR (use CAST or CONVERT) .
  2. Prepend the converted number with the maximum number of zeros you require (either with a fixed length string like '00000000', or use the REPLICATE function).
  3. Take the right most characters of the required length using the RIGHT function.

You can do similar things with LEFT and padding (for instance with spaces to the right).

One example:

SELECT RIGHT('0000000000' + CAST(31415 AS VARCHAR(10)), 10) AS PaddedPiInteger
SELECT LEFT(CAST(31415 AS VARCHAR(10)) + '          ', 10) AS PaddedPiInteger

–jeroen

via SQL SERVER – Pad Ride Side of Number with 0 – Fixed Width Number Display « SQL Server Journey with SQL Authority.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: