Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

Jmills040
6 - Meteoroid

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!

5 REPLIES 5
MarqueeCrew
20 - Arcturus
20 - Arcturus
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-...

 

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Jmills040
6 - Meteoroid

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
11 - Bolide

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 ])(.+)(\.)(.+$)

 

Capture.PNG

MarqueeCrew
20 - Arcturus
20 - Arcturus

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

 

\u  is uppercase letter

\l   is lowercase

 

lots of options :)

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
SophiaF
Alteryx
Alteryx

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

 

yyyy-MM-ddThh:mm:ss

datetime.png

Sophia Fraticelli
Senior Solutions Architect
Alteryx, Inc.
Labels