The correct regex to remove the first character?

Golfoholic · 2021-11-22T20:05:02+00:00

There was an error rendering this rich post.

I have a column( that illustrates a dollar amount but is in string format. What I am trying to do is to remove the dollar sign ($) that is the very first character in each row, so i am only left with the numbers. I have tried the left/right substring but each dollar amount is different length so I have ran in to issues.

Find more posts tagged with

Magic ETL

Data Flows

Accepted answers

MichelleH

Hi @user048760 ,

If the text you want to remove is always the "$" symbol then you could use a Replace Text tile in MagicETL to just remove that character, regardless of the length.

Then you would need use an Alter Columns tile to change the datatype of your column from string to a decimal.

All comments

GrantSmith

REGEXP_REPLACE(`field`,'^\$(.*)$', '$1')

MichelleH

Hi @user048760 ,

If the text you want to remove is always the "$" symbol then you could use a Replace Text tile in MagicETL to just remove that character, regardless of the length.

Then you would need use an Alter Columns tile to change the datatype of your column from string to a decimal.

MarkSnodgrass

You should have the formula tile available to you now that Magic ETL 2.0 is live. You can then do this in a formula tile to remove the $ and convert it to a decimal:

CAST(REPLACE(`fieldname`,'$','') as decimal)

GrantSmith

Alternatively you can also use a formula tile to remove the first character by using RIGHT and LENGTH

RIGHT(`field`, LENGTH(`field`)-1)