How do I search for rows containing specific text and replace the information in that row?

Takalla · 2024-03-13T23:38:22+00:00

There was an error rendering this rich post.

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!

Find more posts tagged with

Magic ETL

Formulas

Data Flows

Accepted answers

GrantSmith

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.

All comments

GrantSmith

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.

Takalla

Thank you! It works! :)