String Operations

damen
damen Contributor
in Magic ETL

Hi all,

I have a column that either has 7 or 8 digits in it. I need it to have exactly 10 everytime

I am working in magic ETL and am wondering how I can create logic to see if the column has 7 or 8 then add 2 or 3 leading 0s to the column depending on which case is true.

Any suggestions?

If this helps, feel free to agree, accept or awesome it!

Tagged:

Best Answers

  • ColemenWilson
    ColemenWilson Coach
    edited May 2024 Answer ✓

    CASE WHEN CHAR_LENGTH(`yourField`) = 7 THEN CONCAT('000',`yourField`) WHEN CHAR_LENGTH(`yourField`) = 8 THEN CONCAT('00',`yourField`) ELSE `yourField` END

    Note: this wont work in a beastmode, you'll have to use this in a formula tile in MagicETL

    If I solved your problem, please select "yes" above

  • MichelleH
    MichelleH Coach
    Answer ✓

    @damen It sounds like the LPAD function in the formula tile is what you're looking for. In your case you would want LEN = 10 and PADSTR = '0'

    image.png

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited May 2024 Answer ✓

    CASE WHEN CHAR_LENGTH(`yourField`) = 7 THEN CONCAT('000',`yourField`) WHEN CHAR_LENGTH(`yourField`) = 8 THEN CONCAT('00',`yourField`) ELSE `yourField` END

    Note: this wont work in a beastmode, you'll have to use this in a formula tile in MagicETL

    If I solved your problem, please select "yes" above

  • MichelleH
    MichelleH Coach
    Answer ✓

    @damen It sounds like the LPAD function in the formula tile is what you're looking for. In your case you would want LEN = 10 and PADSTR = '0'

    image.png
  • damen
    damen Contributor

    @ColemenWilson @MichelleH

    Both of those worked. Thanks to you both!

    If this helps, feel free to agree, accept or awesome it!

Categories