Regex replace digital and string

[cbz]

HI

I have some file name like "actual.waterfall.201802.json" where i want it to be "actual.waterfall"

How can i do it with regex_replace formula?

Thanks

[BenMoss · Accepted solution]

Here is the regex replace formula to remove the suffix.

REGEX_Replace([Field1],".\d+.json","")

I would perhaps consider using the text to columns to with a . delimiter and then stitch back together the first two fields that you want.

Alternatively you can use the regex tool with the 'parse' criteria to pull out this first part.

All three examples are included in the attached workflow.

Ben

[cbz]

Hi ben

Thanks for the solution, it works! I will consider to use test to column in the future.

Just one more question, I saw in other solution in regex_replace sometime they use [] or () to separate the expression and i have tried similar thing but it doesn't work.

what is the [] and () actually do?

Thanks

Chunbin

[BenMoss · Accepted solution]

I'm no regex expert but I would advise you to check out the documentation here: https://help.alteryx.com/11.7/RegEx.htm

As far as I understand curved brackets () are used to 'tokenize' differnt parts of a string you are looking to parse.

For instance, when using the regex statement below on our example in  the regex tool, configured to 'parse mode'

([[:alpha:]]+)\.([[:alpha:]]+)\.(\d+)\.json

It will return us three columns.

The 1st column will be alpha characters up to the first decimal point.

The 2nd column will be alpha characters from the first decimal point to the second decimal point.

And finally the third column will be our digits between the second decimal point and the third decimal point.

As we have not marked json with brackets we get no field for this part.

In contrast square brackets are used to specify individual characters, so basically an or statement.

So if I perform a regex replace with the formula

regex_replace(Field1,"[abc]","")

Then it will remove any instance of the character a, b or c with no character.

I hope this helps!

Ben

[cbz]

Thanks ben, that is helpful.