Cleanse and Transform with leading zeroes

Toad Data Point & Toad Intelligence Central
1

So I have a CSV file that has a column of integers. I convert it into a string now I dont know how I can take that and add leading zeros base don the length of the current value.
So for example
if I have the number 5 > 00005

if I have 12 > 00012

if i have 356 > 00356

2

Hello,

Thank you for posting on the forum.

After converting the column to a string, you can create a calculated column that would concatenate '000' to your newly converted string column, and then you can place that calculated field in a desired position (by dragging and dropping it in the appropriate position in the Tranform and Cleanse data grid). Please take a look at the image below.

image
image1496×843 111 KB

3

not exactly what I want because the number of leading zeroes depends on the current number if charcters.
i gave a example
if I have
5 then it should 00005

if i have 12 then it should be 00012

if I have 356 then I should get 00356

4

In that case, your input for the calculated column should be:
SubStr(Concat('00000', [column_name]), Length([column_name]) + 1, 5)

Allow me explain:

  • Concat('00000', column_name) - This section prepends '00000' to your value
  • Length(column_name) - this section gets the original length of your converted ID column
  • SubStr(..., start, length) - Everything is wrapped inside of a SubStr function, that extracts the last 5 characters (this should be a fixed number of characters that you are expecting in your ID column, and that would ensure the correct number of characters and leading zeros)

image
image1256×773 79.9 KB