Hi FileRequired Output/tenants/ct/108251/prod/rec/processing/deufx/DEUFX_REAL_20220903458756.20221201DEUFX_REAL/tenants/ct/108251/prod/rec/processing/olympic/APR_TLM_FXCASH_2022_05_15_SYS_Fwd_20221545663348.csvAPR_TLM_FXCASH_SYS_Fwd/tenants/ct/108251/prod/rec/processing/apac/GLASS_APAC_CDGL_DAILY_20220415_2026556411478_EXEMPTION_20224547788955.txtGLASS_APAC_CDGL4_DAILY/tenants/ct/108251/prod/rec/processing/apac/GLASS_APAC_CDGL4_2022_03_03_20221545445568.csvGLASS_APAC_CDGL/tenants/ct/108251/prod/rec/processing/idda/t240287bl_1298672_20220803_202284775654.txtt240287Bl_1298672/tenants/ct/108251/prod/rec/processing/idda/OPICS10390_BAL_KOR_20220407101255_20224545565677.txtOPICS10390_BAL_KOR/tenants/ct/108251/prod/rec/processing/sap_fv/s4_idn_sap_abcd_tcs_future_20221015_20224545888886.csvs4_idn_sap_abcd_tcs_future/tenants/ct/108251/prod/rec/processing/sap_fxrates/frontier_sgp_xrates_d_sap_20221105_20221234567812.datfrontier_sgp_xrates_d_sap/tenants/ct/108251/prod/rec/processing/bda/BDA3441_20220215_0345_20220214_20220214204827.txtBDA3441_0345/tenants/ct/108251/prod/rec/processing/bda/BDA3441_20220415_060_20220215_20220214452827.txtBDA3441_060

Regex requirement

File	Required Output
/tenants/ct/108251/prod/rec/processing/deufx/DEUFX_REAL_20220903458756.20221201	DEUFX_REAL
/tenants/ct/108251/prod/rec/processing/olympic/APR_TLM_FXCASH_2022_05_15_SYS_Fwd_20221545663348.csv	APR_TLM_FXCASH_SYS_Fwd
/tenants/ct/108251/prod/rec/processing/apac/GLASS_APAC_CDGL_DAILY_20220415_2026556411478_EXEMPTION_20224547788955.txt	GLASS_APAC_CDGL4_DAILY
/tenants/ct/108251/prod/rec/processing/apac/GLASS_APAC_CDGL4_2022_03_03_20221545445568.csv	GLASS_APAC_CDGL
/tenants/ct/108251/prod/rec/processing/idda/t240287bl_1298672_20220803_202284775654.txt	t240287Bl_1298672
/tenants/ct/108251/prod/rec/processing/idda/OPICS10390_BAL_KOR_20220407101255_20224545565677.txt	OPICS10390_BAL_KOR
/tenants/ct/108251/prod/rec/processing/sap_fv/s4_idn_sap_abcd_tcs_future_20221015_20224545888886.csv	s4_idn_sap_abcd_tcs_future
/tenants/ct/108251/prod/rec/processing/sap_fxrates/frontier_sgp_xrates_d_sap_20221105_20221234567812.dat	frontier_sgp_xrates_d_sap
/tenants/ct/108251/prod/rec/processing/bda/BDA3441_20220215_0345_20220214_20220214204827.txt	BDA3441_0345
/tenants/ct/108251/prod/rec/processing/bda/BDA3441_20220415_060_20220215_20220214452827.txt	BDA3441_060

Data Investigation

Accepted answers

flying008

Hi, @BRRLL99

If you explained your match rule with need clean, maybe can fast get the want.

Please try this formula with a new field [Required Out] :

REGEX_Replace(REGEX_Replace([File], ".*\/([[:alnum:]_]+?)(_20\d{6,})?_20\d{10,}.[[:alnum:]]+$", '$1'), "(_20\d{2}_?\d{2}_?\d{2}|_?\d{5,}_[A-Z]+$)", "")

*********

If it can help you , please mark it as a solution for share more.

All comments

philipmannering

The Regex way to do this would be to use the Regex Tool set to Parse with expression: (\w*?)_2022

philipmannering

I've just noticed this hasn't worked for record 2. Maybe you could describe the logic on which part of the string to include?

BRRLL99

please send solution file

Kenda

@BRRLL99

@PhilipMannering's response is very close to what you're looking for. After looking at your data and the pattern in the values, look at his screen capture to try to re-create the expression he gave then edit to fit your data. The Regex Tool's help page may be helpful to reference.

BRRLL99

row no:2, & 3

is not matching with expected output

flying008

Hi, @BRRLL99

If you explained your match rule with need clean, maybe can fast get the want.

Please try this formula with a new field [Required Out] :

REGEX_Replace(REGEX_Replace([File], ".*\/([[:alnum:]_]+?)(_20\d{6,})?_20\d{10,}.[[:alnum:]]+$", '$1'), "(_20\d{2}_?\d{2}_?\d{2}|_?\d{5,}_[A-Z]+$)", "")

*********

If it can help you , please mark it as a solution for share more.

DawnDuong

hi @BRRLL99

It appears to us me that you are trying to get all the parts that contain at least 1 word character (parts are separated by underscored _) after the last slash. Is that indeed what you are trying to achieve?

As with every Regex problem and solution, the first step is to articulate the rule that you need then design the expression that meet the requirements. Feel free to check out this presentation by Mark Frisch to get started:

https://community.alteryx.com/t5/Inspire-Presentations/To-RegEx-or-Not-to-RegEx-That-Is-the-Question/ta-p/808271

Regards,

Dawn.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335