Archive for June, 2016

Using REG_EXTRACT

Posted: June 1, 2016 in Informatica

REG_EXTRACT can be used to extract a part of the string using a pattern.

Lets see the below example
01/05/2014 i have this input date field. i want to split into 3 parts.

01 to output port 1
05 to output port 2
2014 to output port 3

Use the below logic in output port 1 (it will get you 01)
REG_EXTRACT(SUBSTR(Date,0,9),'(\d{2})/(\d{2})/(\d{4})’,1)

Use the below logic in output port 2 (it will get you 05)
REG_EXTRACT(SUBSTR(Date,0,9),'(\d{1})/(\d{2})/(\d{4})’,2)

Use the below logic in output port 3 (it will get you 2014)
REG_EXTRACT(SUBSTR(Date,0,9),'(\d{1})/(\d{2})/(\d{4})’,3)

If month is string use below (ex:- jun/20/2014 )

REG_EXTRACT(SUBSTR(Date,0,9),'((\w+))/(\d{2})/(\d{4})’,1)
this will give the output ‘jun’

Advertisements