Reg. Expression to replace a list of names with a single character.

Golfoholic · 2021-10-14T22:14:17+00:00

There was an error rendering this rich post.

I have a column that is in a string format, and of course contains names. Majority of the rows are an empty string. What I am looking for is to take the rows that do contain a name and replace them with a 1.

i have been trying to use replace text tile then trying to use a regular expression in the 2nd step, then replace found term with a 1. I am not sure of a reg. ex. that would identify all the different names.

Find more posts tagged with

REGEX

Magic ETL

Data Flows

Accepted answers

GrantSmith

Hi @user048760

Are Just names included in your column and you want to blank it out or is there other information in that column that you need to retain except for the name? If it contains just the name and you want to set it to one if it is populated you could utilize a formula tile in Magic 2.0 with this code:

CASE WHEN COALESCE(`Reserved By`,'') <> '' THEN 1 END

All comments

GrantSmith

Hi @user048760

CASE WHEN COALESCE(`Reserved By`,'') <> '' THEN 1 END

MarkSnodgrass

I like to use the LENGTH and TRIM functions to test for this, which you can do in using the Add Formula tile in Magic ETL 2.0 or in a beast mode. It would look like this

CASE WHEN LENGTH(TRIM(`Reserved By`)) >= 1 THEN 1 ELSE `Reserved By` END

Hope this helps.

jaeW_at_Onyx

It sounds like "if there is text then 1.

Cleanest logic will be

case when text is not null then 1 else 0 end.

Golfoholic

@GrantSmith @MarkSnodgrass @jaeW_at_Onyx You guys are awesome. I appreciate all the insight!