How do I search for rows containing specific text and replace the information in that row?
I have a column in my ETL dataset that is named "City", in that column there are multiple city names however they have been put in in all different ways (ie: st george, St. George, ST George). I would like to search that column for rows containing 'george' and replace the information in that row with 'St. George'. I've figured out how to replace just the word I searched for but I can't figure out how to replace all the information in that row.
I have multiple different cities to do this with, St. George is just one example. I have created an ELT to do some other transformations with my data so I'd like to add this transformation in there as well.
I know there is a way to filter the column using the 'contains' comparison however I'm not trying to filter, I'm trying to replace text.
Can someone give me some advice on how to make this work?
Thanks!
Best Answer
-
You can use a formula tile and a case statement to set the value:
CASE WHEN REPLACE(`City`, '.', '') ILIKE '%st george%' THEN 'St. George' ELSE `City` END
You can have multiple different WHEN clauses to handle other scenarios.
ILIKE is the case-insensitive version of LIKE where you're matching based on a string with the % serving as a wild card.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2
Answers
-
You can use a formula tile and a case statement to set the value:
CASE WHEN REPLACE(`City`, '.', '') ILIKE '%st george%' THEN 'St. George' ELSE `City` END
You can have multiple different WHEN clauses to handle other scenarios.
ILIKE is the case-insensitive version of LIKE where you're matching based on a string with the % serving as a wild card.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2 -
Thank you! It works! :)
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive