Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'd like to write an expression where if the fourth character of a string is '1', it is replaced with a 'P'. I'm able to replace the character and keep the expression before the fourth character, but am having trouble keeping the latter half of the string because if there is another '1' later in the string, the subfield portion of the expression picks those up as well. How to get the rest of the text after a certain character? I'm sure there are better ways to accomplish what I wrote. Any improvements are also greatly appreciated. Thank you!
This is the expression I used and I'm trying to fill in the blank space. I used the subfield expression below to fill in the space but it is not functioning correctly.
=if(mid([Animal Name], 4,1) = '1', left([Animal Name], 3)&replace(mid([Animal Name], 4,1), '1', 'P'&_______), [Animal Name])
=subfield([Animal Name], mid([Animal Name], 4, 1), -1)
Animal Name |
Desired Output |
CHI1ABFBW |
CHIPABFBW |
COWLABFBW |
COWLABFBW |
PIG12351 |
PIGP2351 |
SH11IO1BB |
SH1PIO1BB |
A111WBI154 |
A11PWBI154 |
I think this should do the trick:
=if(mid([Animal Name], 4, 1) = '1',
left([Animal Name], 3) & 'P' & right([Animal Name], len([Animal Name])-4),
[Animal Name])
old – old substring you want to replace. new – new substring which would replace the old substring. count – (Optional ) the number of times you want to replace the old substring with the new substring.
another solution might be:
=If([Animal Name] like '???1*', Left([Animal Name],3)&'P'&Mid([Animal Name],5), [Animal Name])
@Sara1 Please see the code below that I have used in the load Editor:
NoConcatenate
Temp:
Load * inline [
Animal Name
CHI1ABFBW
COWLABFBW
PIG12351
SH11IO1BB
A111WBI154
];
NoConcatenate
Temp1:
Load *,
if(mid([Animal Name],4,1)='1',
left([Animal Name],3)&replace(mid([Animal Name],4,1),'1','P')&right([Animal Name],len([Animal Name])-4),
[Animal Name]) as New_string
Resident Temp;
Drop table Temp;
Exit Script;
IF this resolves your issue, please like and accept it as a solution.