pad with leading zeros
Sherry asked 10 years ago
I have a .txt source file. In my select statement for the data source, I want to pad a string field with leading zeros. Example: I may have values 1234 and 411 and 22. I need 411 to be 0411 and 22 to be 0022. The field should always be four characters – adding whatever number of leading zeros are required to get there. Thanks in advance.
[quote=Sherry]I have a .txt source file. In my select statement for the data source, I want to pad a string field with leading zeros. Example: I may have values 1234 and 411 and 22. I need 411 to be 0411 and 22 to be 0022. The field should always be four characters – adding whatever number of leading zeros are required to get there. Thanks in advance.[/quote]
Do it in the select statement…
Select (right('0000' + MyFieldName,4) as MyZeroFilledField
HTH,
Glenn
This isn’t working for me, I’m trying to create a simple calculation to fill a field with 3 characters with leading zeros. The value of this field is 3, but it needs to be 003. Trying to use your recommendation, I came up with this: return fn.LEFT(‘000’,_DEPARTMENT,4 ), however, this isn’t working. Please advise.
Hi Elsa,
To get the answer you’re looking for, you would need to use:
fn.RIGHT(‘000’ + _DEPARTMENT, 3)
Cheers,
Rod