date parse

[tandon]

Hi, I am trying to parse date from string

I have string where date and numbers embedded like 5/31/20210010

Last 4 digits are numbers which can be either 1,2 ,3 or 4 digits. 

I used below to parse it and then thinking to concatenate date for mm/dd/yyyy and number in separate column.

(\d+)\/(\d+)\/(\d{4})(\d+)

Is their any better way to solve it?

[DataNath]

Although you say the number part at the end can vary in length, as long as your date format before that is always consistent then that looks like a perfectly reasonable way of doing it! There are obviously other ways but I wouldn't say there's a problem with this unless then pattern of the actual date part can change.

Only thing I'd mention is that, if you want to discard of the number on the end, just drop the last capture group i.e. finish the expression at (\d{4}).

[tandon]

Yes. I was just exploring if there is way to avoid concatenate as I am extracting mm, dd, yyyy and number in separate columns.

Can we read string backwards using regex? i..e if I want to read last 4 digit only. I know, its valid only if it;s fixed for 4 char only.

[binuacs]

@tandon You can also use Right() function to extract last four characters 

Right([Field],4)

[MarqueeCrew · Accepted solution]

@tandon 

I think that you're looking for date time specifiers as:

DateTimeParse(%m/%d/%Y)

For the Last digits...

REGEX_Replace([Field1], ".*\/\d{4}(\d+)", '$1')

This regular expression finds the remaining digits after /9999 a slash followed by 4 digits.  All remaining digits are captured.

Cheers,

Mark

[DataNath]

If I'm understanding your ask properly then you just want the date part in one column and then the number in another? Rather than a day, month, year and number column? If so, you just need to include the whole date part in one capture group:

(\d+\/\d+\/\d{4})(\d+)

You can then parse the date by using the dateparse function highlighted by @MarqueeCrew above!