Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

WITH 
test_data AS
(
SELECT 'GALLETAS SALADAS' AS product FROM dual UNION ALL
SELECT 'GALLETAS DE LECHE' AS product FROM dual UNION ALL
SELECT 'GALLETAS CRACKER CON SABOR A MANTEQUILLA' AS product FROM dual UNION ALL
SELECT 'GALLETAS INTEGRALES CON AVENA' AS product FROM dual UNION ALL
SELECT 'GALLETAS WAFER CON CREMA DE CHOCOLATE' AS product FROM dual UNION ALL
SELECT 'GALLETAS WAFER CON CREMA SABOR A VAINILLA' AS product FROM dual UNION ALL
SELECT 'GALLETAS DE MANTEQUILLA' AS product FROM dual UNION ALL
SELECT 'GALLETAS SALTIN' AS product FROM dual UNION ALL
SELECT 'GALLETAS DE CHOCOLATE CON CREMA SABOR A VAINILLA' AS product FROM dual UNION ALL
SELECT 'GALLETAS CON SABOR A VAINILLA' AS product FROM dual UNION ALL
SELECT 'GALLETAS CON CREMA SABOR A FRESA' AS product FROM dual UNION ALL
SELECT 'BARQUILLOS SABOR A CHOCOLATE CON CREMA SABOR A LECHE' AS product FROM dual UNION ALL
SELECT 'PASTEL CON CREMA SABOR A VAINILLA' AS product FROM dual UNION ALL
SELECT 'GALLETAS DE LECHE CON CREMA DE AVENA Y SABOR A LECHE' AS product FROM dual UNION ALL
SELECT 'GALLETAS CON CREMA SABOR A YOGURT FRESA' AS product FROM dual UNION ALL
SELECT 'GALLETAS CON AVENA Y CALCIO' AS product FROM dual UNION ALL
SELECT 'BARQUILLOS CON CREMA SABOR A FRESA' AS product FROM dual UNION ALL
SELECT 'BARQUILLOS RELLENOS CON CREMA DE CHOCOLATE Y AVELLANA' AS product FROM dual UNION ALL
SELECT 'GALLETAS MINI WAFER CUBIERTAS CON SABOR A CHOCOLATE' AS product FROM dual UNION ALL
SELECT 'GALLETAS WAFER CON CREMA SABOR A AREQUIPE (DULCE DE LECHE)' AS product FROM dual
)SELECT CASE 
        WHEN (INSTR(test_data.product,' ',1,2) = 0) THEN
             substr(test_data.product, 1, LENGTH(test_data.product))  
        WHEN (INSTR(test_data.product,' ',1, 3) = 0) THEN
             substr(test_data.product, 1, INSTR(test_data.product,' ',1, 2))
        ELSE substr(test_data.product, 1, INSTR(test_data.product,' ',1, 3))
       END
FROM   test_data

CASEWHEN(INSTR(TEST_DATA.PRODUCT,'',1,2)=0)THENSUBSTR(TEST_DATA.PRODUCT,1,LENGTH(TEST_DATA.PRODUCT))WHEN(INSTR(TEST_DATA.PRODUCT,'',1,3)=0)THENSUBSTR(TEST_DATA.PRODUCT,1,INSTR(TEST_DATA.PRODUCT,'',1,2))ELSESUBSTR(TEST_DATA.PRODUCT,1,INSTR(TEST_DATA.PR
GALLETAS SALADAS
GALLETAS DE
GALLETAS CRACKER CON
GALLETAS INTEGRALES CON
GALLETAS WAFER CON
GALLETAS WAFER CON
GALLETAS DE
GALLETAS SALTIN
GALLETAS DE CHOCOLATE
GALLETAS CON SABOR
GALLETAS CON CREMA
BARQUILLOS SABOR A
PASTEL CON CREMA
GALLETAS DE LECHE
GALLETAS CON CREMA
GALLETAS CON AVENA
BARQUILLOS CON CREMA
BARQUILLOS RELLENOS CON
GALLETAS MINI WAFER
GALLETAS WAFER CON