The correct regex to remove the first character?
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.
Best Answer
-
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.
1
Answers
-
REGEXP_REPLACE(`field`,'^\$(.*)$', '$1')
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
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.
1 -
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)
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
Alternatively you can also use a formula tile to remove the first character by using RIGHT and LENGTH
RIGHT(`field`, LENGTH(`field`)-1)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**3
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 602 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 695 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 113 Manage
- 110 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive