Pad field with zero

How can i take a sql result field where I am counting the number of people in the file for a trailer and left pad with zeroes? I have tried a bunch of the expressions shown in several of the answers here but nothing is working. I get

Hi @heidi.andrew,

You can try with the following expression:

"0000000000".slice($COUNT.length) + $COUNT

Let me know if this helps you.

BR,
Aleksandar.

Or if you want to do it in more dynamic way where you specify how long should the field be, you can try the following expresssion:

sl.range(0,10).map(x=>'0').join('').slice($COUNT.length) + $COUNT

Just change the second parameter in sl.range(0,x) .

I KEEP getting the null:


But i did figure out how to show first day of last month and last day of last month!

@heidi.andrew - one other option - a while back, I created a set of library expression files, one of which contains LPAD and RPAD functions. Basically, this simplifies this type of expression and prevents multiple solutions to the problem from different developers.

I hope this helps!

2 Likes

It is because the COUNT is an integer, try with this:

"0000000000".slice($COUNT.toString().length) + $COUNT

i have been in that one a lot but nothing works for me. I tried several iterations of this:
lib.string.lpad(‘123’, 5, ‘0’) yields “00123”

i brought it into a mapper field. Is that the right snap? I changed the " from the curved version and still get the error like this:

Can you share your pipeline? If you have the library referenced in your pipeline then the statement you show should work.

If you have a guaranteed numeric value (integer) you can simply use "%010d".sprintf($COUNT) to get a string out, you can do similar with float values, but you have to be a bit more tricky about it. I believe it follows the Java util formatter specification.

2 Likes

That worked perfectly! You rock!

1 Like

This is the one I always use
Where 9 is the size you want and text2 is you data (-9 negative of size you want)
‘0’.repeat(9).concat(text2).slice(-9)

Explain:

text1 = “000000000”; //length you want

text2 = “1234”;

result = “000000000”.concat(text2);

0000000001234
//returns last number of characters
let result2 = result.slice(-9); //length you want

000001234

‘0’.repeat(9).concat(text2).slice(-9)

3 Likes