Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to change during the loading process a 0 (zero) for a 'O' in the middle of the string.
As an exemple, I have the following data: C.A9.M038.000.000.SIP where I need to change the 0 (the one following the M) to a O.
I try the following options and they doesn't work:
If(mid(EPEP,7,1)=0, Mid(EPEP,1,6)&'O'&right(EPEP,14) as EPEP_2
If(isnull(mid(EPEP,7,1))=-1, Mid(EPEP,1,6)&'O'&right(EPEP,14) as EPEP_2
If(mid(EPEP,7,1)='0', Mid(EPEP,1,6)&'O'&right(EPEP,14) as EPEP_2
EPEP is the name of the field.
Any suggestion? I think the solution must be simple..... but I'm blind for the moment. Thanks in advance for any help.
Patrick.
Sorry, the formula is working, I just place it in the wrong table.
Sorry, the formula is working, I just place it in the wrong table.
The last one worked for me, there was only a closing bracket missing:
LOAD
EPEP,
If(mid(EPEP,7,1)='0', Mid(EPEP,1,6)&'O'&right(EPEP,14)) as EPEP_2
INLINE [
EPEP
C.A9.M038.000.000.SIP
];
and you probably want to keep the original in the else branch:
LOAD
EPEP,
If(mid(EPEP,7,1)='0', Mid(EPEP,1,6)&'O'&right(EPEP,14),EPEP) as EPEP_2
INLINE [
EPEP
C.A9.M038.000.000.SIP
];