STR to Digit

ColinHaze
ColinHaze Member
in Magic ETL

Hi,

Can someone explain this to me? I have a column that have some values that contain '$' making it a string. I am running it through an etl and using str to digit while also trying to keep it a floating point value. My formula is

LONG(STR_DIGITS(TRL_MISC3))

However its taking some values like this

image.png

and outputting them like this

image.png


What might be the cause of this?

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    @ColinHaze The STR_DIGITS function removes all non-digit characters, including decimal points. If dollar signs are your only issue, then I would use this function instead so that it only removes the dollar sign character:

    CAST(TRIM(REPLACE(TRL_MISC3,'$','')) as float)

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @ColinHaze The STR_DIGITS function removes all non-digit characters, including decimal points. If dollar signs are your only issue, then I would use this function instead so that it only removes the dollar sign character:

    CAST(TRIM(REPLACE(TRL_MISC3,'$','')) as float)

Categories