String Operations

damen

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?

ColemenWilson

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

MichelleH

@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'

ColemenWilson

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

MichelleH

@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'

damen

@ColemenWilson @MichelleH

Both of those worked. Thanks to you both!