Use an Expression transformation with a couple of variable ports.
Let's assume that the input port is named EMAILADDRESS.
First variable port is v_pos_at, type Integer, with this expression term:
InStr( EMAILADDRESS, '@')
Next variable port is v_full_name, type String(255), with this term:
IIF( v_pos_at > 0, SubStr( EMAILADDRESS, 1, v_pos_at - 1), NULL)
Now a variable port v_pos_underline, type Integer, with this expression term:
IIF( v_pos_at > 0, InStr( v_full_name, '_'), 0)
Finally an output port NAME_ONLY, type String(255), with this term:
v_pos_underline > 0, SubStr( v_full_name, 1, v_pos_underline - 1),
v_pos_at > 0, v_full_name,
The DECODE(TRUE...) is like the IF ... ELSE IF ... ELSE IF... construct in many programming languates, you can indicate arbitrary conditions and the associated return values here.
In my example above, the DECODE first checks whether v_full_name contains an underline character "_"; if so, it returns the string before this underline.
If there is no underline character but the email address is syntactically correct (i.e. it contains a At sign), it returns the part before this At sign as the name part.
Otherwise it obviously is no email address, so nothing is returned.
Personally I wouldn't use REG_EXTRACT() for several reasons in this particular case:
First REG_EXTRACT() is a complex function which eats up fairly much system resources (RAM and CPU time). The method described by me earlier is far less resource-intensive.
Preface for the second point: the respective REG_EXTRACT() call might look like this:
Reg_Extract( emailport, '([^_@]*)[_@]', 1)
Second the regular expression needed here is not too complicated once you understand those regular expressions, but reading it is a different story. And as of my experience it's almost always wise to keep things as simple as possible, in particular in terms of future expansion and maintenance.
Third (again, as of my experience) the method with an EXP is easier to expand and to adapt to future use cases than the REG_EXTRACT() method.
My 2 cents.
Thanks Nico for more clarification.
Yes using REG_EXTRACT() may have some challenges. It completely depends on if you want to use a function or you want to add a new transformation, depending upon that both can be used.