DEV Community

Cover image for SQL-Quick tip #3 - Prepending zeroes
Allan Simonsen
Allan Simonsen

Posted on

3 2

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

Hostinger image

Get n8n VPS hosting 3x cheaper than a cloud solution

Get fast, easy, secure n8n VPS hosting from $4.99/mo at Hostinger. Automate any workflow using a pre-installed n8n application and no-code customization.

Start now

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay