SQL -Report missing string when running a query

Transact-SQL
1

I am new to sql and this forum, looking for some help.
Thank you for your cooperation

Table name Group
Column - name

SBM-TRANS-ROSE Exective Staff | MCB-FRAM-ROUG Personnel executif
ATM-CPES-ROSE Mgt | VAC-FRAM-VERT Gestion
SBM-TRANS-ROSE-MANG TSFD | MCB-FRAM-ROUG-TOMG ESAN
SBM-TRANS-ROSE-MANG OT | MCB-FRAM-ROUG-TOM LAX

Required Output

SBM-TRANS-ROSE | MCB-FRAM-ROUG
ATM-CPES-ROSE | VAC-FRAM-VERT
SBM-TRANS-ROSE-MANG-TSFD | MCB-FRAM-ROUG-TOMG-ESAN
SBM-TRANS-ROSE-MANG-OT | MCB-FRAM-ROUG-TOM-LAX

Select G.name,
SUBSTR(G.NAME,1,INSTR(G.NAME,' ')-1) || ' | ' || SUBSTR(SUBSTR(G.NAME,INSTR(G.NAME,' | ',-3)+3),1,INSTR(SUBSTR(G.NAME,INSTR(G.NAME,' | ',-3)+3),' ')-1) as txt4

from Group G

My issue when I run the query I got the following ,
SBM-TRANS-ROSE | MCB-FRAM-ROUG
ATM-CPES-ROSE | VAC-FRAM-VERT
SBM-TRANS-ROSE-MANG | MCB-FRAM-ROUG-TOMG
SBM-TRANS-ROSE-MANG | MCB-FRAM-ROUG-TOM

I am missing TSFD and ESAN in the third row
OT and LAX in the fourth row

2

Please be aware that this is a SQL Server forum. Your query uses a different dbms (db engine, like Oracle). Some people here may still be able to help you, if they happen to know your dbms, but you might have better luck on a forum dedicated to the dbms you are using.