Padding Data In Multiple Ways

One of the many fun things we’re asked to do as a person who works with data is that we’re asked to do some manipulation of data – especially when we’re asked to pad data with 0’s to make a string longer that is numerical.

For example, I’ve been asked many times to provide data in the format of 000314159265 instead of 314159265. This sometimes causes confusion with those people who are viewing the data in Excel – since it by default removes the “0” in front of the number.

Today, we’re going to be working with a very small data set, all with different lengths – so that we can see the left padding work as expected.

DROP TABLE IF EXISTS #Numbers

CREATE TABLE #Numbers
( NumericValue VARCHAR(5) PRIMARY KEY)

INSERT INTO #Numbers
VALUES (1), (37), (938), (1250), (38593)

One way that we can show off our lovely padded data is with the following code:

SELECT NumericValue, 
	REPLICATE('0', 5 - DATALENGTH(NumericValue)) + NumericValue AS PaddedValue
FROM #Numbers
ORDER BY PaddedValue

Here, by use of the REPLICATE function, I am telling SQL Server that I want to see many “0”‘s. At most I will see 5 of them since that is the longest length of the data I have available. I then use the DATALENGTH function to make sure that I only have the number of “0”‘s that I need to the left of the value. I complete that string with the value itself.

A few things to note here:

• Notice that the NumericValue is a varchar. This allows the user to change the “0” to an “A” or any other string value.
• Notice that the 0 used is truly a “0” – a string 0. This also allows you to use an “A” or any other string value.
• If you remove the “- DATALENGTH(NumericValue)” from the code, it gives you a very different result. (I leave it to the reader to see what that does)
• To make sure that this process works, notice that I have used DATALENGTH instead of the LEN function. This is because you may need to use UNICODE data – which is twice the length of a varchar string. (If that sentence doesn’t make sense to you, tune in next week to see a more in depth explanation)

Now, reading all of that, I know you’re thinking to yourself “Self” – for that’s what you call yourself – you said, “Self, I’m probably going to forget that in a few days. That’s quite a number of new functions.”

Well, don’t go too crazy, because as there are many times with SQL Server – there are many ways to make this happen.

SELECT NumericValue, RIGHT('00000' + NumericValue, 5) AS PaddedValue
FROM #Numbers
ORDER BY PaddedValue

Notice – this time I used a simple string “00000” – 5 “0”‘s. I then shoved the NumericValue data onto the end of the string. Finally, I just took the 5 rightmost characters.

Both ways work well.

Always remember – there is usually more than one way to make something happen in code. It’s up to you to make it happen the best way. If it’s not the right way one time, it could be the next.

Until next time my friends! Remember to try something different and keep finding different ways to solve a problem!