Forum Discussion

heidi_andrew's avatar
heidi_andrew
Contributor
3 years ago
Solved

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

  • Aleksandar_A's avatar
    Aleksandar_A
    3 years ago

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

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

10 Replies

  • jcornelius's avatar
    jcornelius
    New Contributor III

    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)

  • @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!

  • 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.

  • Hi @heidi.andrew,

    You can try with the following expression:

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

    Let me know if this helps you.

    BR,
    Aleksandar.

    • Aleksandar_A's avatar
      Aleksandar_A
      Contributor III

      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) .

    • heidi_andrew's avatar
      heidi_andrew
      Contributor

      I KEEP getting the null:


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

      • Aleksandar_A's avatar
        Aleksandar_A
        Contributor III

        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.