Replace part of a compound string using MySQL 5.5

Question

I need to replace part of a compound string.

List of compound strings:

+-------+---------------------------------+
| cod   | ub                              |
+-------+---------------------------------|
| 1642  |NORMAN|BATES|RO|Eserc|CAR|(Ex: ) |
| 1641  |Marion|Crane|VR|Eserc|RET|(Ex: ) |
| 1640  |Lila  |Crane|RO|Eserc|PLS|(Ex: ) |
| 1639  |Sam   |Looms|RO|Eserc|CAR|(Ex: ) |
| 1638  |Milton|Arbog|VR|Eserc|CAR|(Ex: ) |
+-------+---------------------------------+
...

I want to replace the fifth element in each serialised record.

This string for example

"NORMAN|BATES|RO|Eserc|CAR|(Ex: )"

On this case, I need replace the value of the string from "CAR" to

"FOO"

For this return

"NORMAN|BATES|RO|Eserc|FOO|(Ex: )"

I have tried using an SELECT SQL query here

SELECT 
REPLACE(
SUBSTRING_INDEX(SUBSTRING_INDEX(
"NORMAN|BATES|RO|Eserc|CAR|(Ex: )"
,"|", 2)
,"|", -1),
SUBSTRING_INDEX(SUBSTRING_INDEX(
"NORMAN|BATES|RO|Eserc|CAR|(Ex: )"
,"|", 2)
,"|", -1), "FOO") x;

And I have this output.

+-----+
| x   |
+-----+
| FOO |
+-----+
1 row in set (0.10 sec)

But in query UPDATE SQL the whole string is replaced by the value "FOO"

UPDATE `tbl_d` 
SET Ub = REPLACE(
SUBSTRING_INDEX(SUBSTRING_INDEX(
"NORMAN|BATES|RO|Eserc|CAR|(Ex: )"
,"|", 2)
,"|", -1),
SUBSTRING_INDEX(SUBSTRING_INDEX(
"NORMAN|BATES|RO|Eserc|CAR|(Ex: )"
,"|", 2)
,"|", -1), "FOO") WHERE Cod = "1642";

Any help really appreciated.

NORMAN|BATES|RO|Eserc|FOO|(Ex: )

Answer 1

Perhaps substring_index might work for you

select   concat(
            substring_index('NORMAN|BATES|RO|Eserc|CAR|(Ex: )','|',4) ,
            '|FOO|',
            substring_index('NORMAN|BATES|RO|Eserc|CAR|(Ex: )','|',-1)
            ) aa;

+----------------------------------+
| aa                               |
+----------------------------------+
| NORMAN|BATES|RO|Eserc|FOO|(Ex: ) |
+----------------------------------+
1 row in set (0.001 sec)