0Recently, I came across quite a common question – as part of a data migration, someone wanted to pad integers with zeroes. There are various variations to this question, namely:
How do I pad zeroes to convert an integer to a fixed length string?
How do I pad zeroes before an integer?
How to I pad blank spaces before an integer?
All of these questions have quite a simple solution, which I am going to present before you today.
The script demonstrates the process of padding the required values to a set of integers in a test table. The script:
- Converts the Integer to a string
- Appends this string representation of the integer to the padding string
- Finally, returns the required number of characters from the right of the string
For the purposes of this demo, I have shown the result with two padding characters – a zero (0) and an asterisk (*).
Have you ever faced such a requirement as part of a data migration or an integration? Do you use a similar approach? Do share your thoughts and suggestions in the space below.
--Pad zeroes in string representation of a numberUSE tempdb;GO--Safety CheckIF OBJECT_ID('dbo.TestTable','U') IS NOT NULLBEGIN DROP TABLE dbo.TestTable;ENDGO--Create the test tablesCREATE TABLE dbo.TestTable (RecordId INT NOT NULL IDENTITY(1,1), RecordValue INT NULL );GO--Populate some test dataINSERT INTO dbo.TestTable (RecordValue)VALUES (123), (1023), (NULL);GO/**************** PADDING CHARACTER: ZERO (0) ****************************/--Change the padding character and the number of strings as requiredDECLARE @requiredStringLength INT = 10;DECLARE @paddingCharacter CHAR(1) = '0'--The script:--1. Converts the Integer to a string--2. Appends this string representation of the integer to the padding string--3. Finally, returns the required number of characters from the right of the stringSELECT RecordId, RecordValue AS OriginalValue, RIGHT( (REPLICATE( @paddingCharacter, @requiredStringLength ) + CAST(RecordValue AS VARCHAR(20)) ), @requiredStringLength ) AS PaddedValueFROM dbo.TestTable AS tt;GO/* RESULTSRecordId OriginalValue PaddedValue----------- ------------- ------------1 123 00000001232 1023 00000010233 NULL NULL*//**************** PADDING CHARACTER: ASTERISK (*) ****************************/--Change the padding character and the number of strings as requiredDECLARE @requiredStringLength INT = 10;DECLARE @paddingCharacter CHAR(1) = '*'--The script:--1. Converts the Integer to a string--2. Appends this string representation of the integer to the padding string--3. Finally, returns the required number of characters from the right of the stringSELECT RecordId, RecordValue AS OriginalValue, RIGHT( (REPLICATE( @paddingCharacter, @requiredStringLength ) + CAST(RecordValue AS VARCHAR(20)) ), @requiredStringLength ) AS PaddedValueFROM dbo.TestTable AS tt;GO/* RESULTSRecordId OriginalValue PaddedValue----------- ------------- ------------1 123 *******1232 1023 ******10233 NULL NULL*/
