How to use RegEx to remove text in a string and replace text with the updated expression

[Jmills040]

I have a date field coming in from a csv file that stores the dates as '2013-12-11T05:18:37.419+00:00' (V_WString type) and I need to get the date to look like '2013-12-11 05:18:37'. I know the RegEx Tool has the capability to do this, but I cannot for the life of me figure out how to set up the regular expression. Please Help!

[MarqueeCrew]

Replace(left([Input Date],19),"T",' ')

regex_replace([Input Date],"(.*)T(.*)\..*",'$1 $2')

The first formula takes the left 19 characters and then replaces the T with a space.

The second formula takes everything up to the T and everything between the T and the Period and puts a space between those groups.

Cheers,

Mark

https://community.alteryx.com/t5/Alteryx-Analytics-Excellence/Excellence-Awards-2017-Adam-Rant-From-...

[Jmills040]

Thanks for the quick response! Both options worked. Using the RegEx tool the Regular Expression would be (.*)T(.*)\..* with the output method as Replace and the Replacement Text set to $1 $2. However, if the letter is not always T how would one account for any random letter?

[anthony · Accepted solution]

Here is an other option - break out into groups and then replace with group 1 SPACE 3

(^.+)(T)(.+)(\.)(.+$)

(^.+) everything from beginning of the line

up to (T) 

(.+) everything after T

up to the (\.) literal period

(.+$) and everything after period - technically this could be be in period group.

... random letter group would be  ([a-zA-Z]) ...eg any of those single letters. You could even add a space ([a-zA-Z ]) should the T be a " " 

(^.+)([a-z ])(.+)(\.)(.+$)

[MarqueeCrew]

If you don't know the letter, you could use:

\u  is uppercase letter

\l   is lowercase

lots of options :)

[SophiaF]

This also can be done with the new DateTime Parse tool in Alteryx 11 using the "Custom" format:

yyyy-MM-ddThh:mm:ss