DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’» is a community of 968,873 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Create account Log in
Cover image for SQL-Quick tip #3 - Prepending zeroes
Allan Simonsen
Allan Simonsen

Posted on

SQL-Quick tip #3 - Prepending zeroes

Sql Server tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Prepending zeroes

Some data formats require that your numbers have prepended zeros so you can get the SQL Server to format the number. The trick is to prepend the number of zeroes to the your int and then chop the resulting string by the number of wanted digits from the right.

But be aware that in optimized production code this might not be the way to go because the server has to do a lot of string manipulating the do this trick. But for testing or one-time queries this trick can be useful.

DECLARE @names TABLE ([Name] VARCHAR(50), [ImportantNumber] INT)

INSERT INTO @names ([Name], [ImportantNumber]) 
VALUES ('Joe', 2), ('Bob', 4), ('Anne', 42), ('Jane', 134)

SELECT [Name], RIGHT('0000' + CAST(ImportantNumber AS VARCHAR(4)),4) AS [ImportantNumber]
  FROM @names
Enter fullscreen mode Exit fullscreen mode

Sql Server Management Studio screenshot

Oldest comments (0)

πŸ‘‹ Hey, my name is Noah and I’m the one who set up this ad. My job is to get you to join DEV, so if you fancy doing me a favor, I’d love for you to create an account.

If you found DEV from searching around, here are a couple of our most popular articles on DEV: