Extract string conditionally

[iYassino]

Hello, 

I want to extract capacity from this exemple :

Exemple :  

from "5L Glaceol Rafinum -5°C" extract  "5"

from "208L w40 Total DZ" extract  "208"

from "0.2L Lubrex Degrippant" extract  "0.2"

Data sample :

0.2L Lubrex Degrippant

0.5L Naftilia Moto 2T

0.7L Eau Déminéralisée ASA

0.9L ATF Pro Fors Dexron DII

0.9L ATF TopAsia

0.9L SAE90 Top Asia Gear oil

1.5L G12 Bendix RED Concentré

1.5L G12+ ATQ Concentré

1.5L G12+ ROWE Concentré

1.5L G13 ATQ Concentré

1KG Graisse Lotos

5L Glaceol Rafinum -5°C

5L Glaciol EGDP

5L Lave Glace HBCH

5L Lave Glace Naftal

5L Lave Glace Pomaria

5L S40 AVIN Force 4s3

5L SAE40 Lotos Ibis Rdeo

5w30 Mannol TS17 UHPD BLUE

10L Nettoyant moteur Etoile Glanze

10w30 Liquimoly Marine 4T

20L 15w40 Mannol E7 TS4 SHPD

20L SAE40 Lotos Superol CB

20L SAE50

20L SAE90 Lotos

60L 5w30 castrol Edge LL

208L 5w30 Castrol EDGE BMW LL-04

208L 5w30 Castrol EDGE C3

208L 5w30 Castrol EDGE LongLife III

208L 15w40 Chempioil Truck SHPD E7

208L 15w40 Rowe Hightec Turbo E7

208L w40 Chempioil

208L w40 Total DZ

AdBlue 5L Tradex

AdBlue ROWE

AdBlue Volkswagen

Ampoule 1 Plomb NARVA Orange PY21W 12V 21W

Ampoule 1 Plomb OSRAM P21W 12V ORIGINAL

Thanks

[MarcoWedel]

Can you describe how you would manually extract those values from given strings?
Based on what rules would you decide which numbers to use?

[iYassino]

Thanks

The values I want to extract all start with numbers and are in the first sentence like "208L". "20L" or "5L" and the "L" defines the Liter,
I just need to have the capacities apart from doing mathematical operations

[JGMDataAnalysis · Accepted solution]

Something like this?

Products:
NOCONCATENATE
LOAD
	RecNo() AS RowIDTemp,
    *
INLINE [
        ProductDescription
        0.2L Lubrex Degrippant
        0.5L Naftilia Moto 2T
        0.7L Eau Déminéralisée ASA
        0.9L ATF Pro Fors Dexron DII
        0.9L ATF TopAsia
        0.9L SAE90 Top Asia Gear oil
        1.5L G12 Bendix RED Concentré
        1.5L G12+ ATQ Concentré
        1.5L G12+ ROWE Concentré
        1.5L G13 ATQ Concentré
        1KG Graisse Lotos
        5L Glaceol Rafinum -5°C
        5L Glaciol EGDP
        5L Lave Glace HBCH
        5L Lave Glace Naftal
        5L Lave Glace Pomaria
        5L S40 AVIN Force 4s3
        5L SAE40 Lotos Ibis Rdeo
        5w30 Mannol TS17 UHPD BLUE
        10L Nettoyant moteur Etoile Glanze
        10w30 Liquimoly Marine 4T
        20L 15w40 Mannol E7 TS4 SHPD
        20L SAE40 Lotos Superol CB
        20L SAE50
        20L SAE90 Lotos
        60L 5w30 castrol Edge LL
        208L 5w30 Castrol EDGE BMW LL-04
        208L 5w30 Castrol EDGE C3
        208L 5w30 Castrol EDGE LongLife III
        208L 15w40 Chempioil Truck SHPD E7
        208L 15w40 Rowe Hightec Turbo E7
        208L w40 Chempioil
        208L w40 Total DZ
        AdBlue 5L Tradex
        AdBlue ROWE
        AdBlue Volkswagen
        Ampoule 1 Plomb NARVA Orange PY21W 12V 21W
        Ampoule 1 Plomb OSRAM P21W 12V ORIGINAL
];

Temp:
NOCONCATENATE
LOAD *,
	If(Right(ProductCapacityTemp, 1) = 'L'
    	AND IsNum(Left(ProductCapacityTemp, Len(ProductCapacityTemp)-1)),
        	Num(Replace(ProductCapacityTemp, 'L', ''))
    ) AS ProductCapacity
;
LOAD *,
	Trim(Replace(SubField(ProductDescription, ' '), '.', ',')) AS ProductCapacityTemp
RESIDENT
	Products
;

LEFT JOIN (Products)
LOAD
	RowIDTemp,
    ProductCapacity
RESIDENT
	Temp
WHERE
	NOT IsNull(ProductCapacity)
    AND RowIDTemp <> Peek(RowIDTemp)
ORDER BY
	RowIDTemp,
    ProductCapacity
;

DROP TABLE Temp;
DROP FIELD RowIDTemp FROM Products;

[iYassino]

just a small clarification, I bring back the "Product Description" data like this:
SQL SELECT "id" as "Product_id",
"last_price_purchase",
"Mark",
"name" as "Products_name",
"price",
"price_purchase",
"product_type" as "Id_Product_type",
"ref",
"ref_aftm",
"ref_oem" as "OEM",
"stock",
"wholesale_price",
"oem_changed"
FROM "mechanical_workshop2". "public".."auto_product";

Postgresql database table "auto_product" column "name" as "Products_name",

in this case, as I modify your scrip, it works perfectly
sorry for the lack of details and thank you very much