WITH t AS ( SELECT 'SLEEVE-INNER DH36 OD:189X11TX75L 150A JS' pname FROM dual UNION ALL SELECT 'SLEEVE-INNER DH36 OD:163X10TX75L 5"(125A' FROM dual UNION ALL SELECT 'ELBOW 45-SR WELD PG370 BE 12.7T 250A MTR' FROM dual UNION ALL SELECT 'PIPE ERW PE STPG370 #80 6M 125A MTR' FROM dual UNION ALL SELECT 'PIPE ERW PE STPG370 #80 6M 150A MTR' FROM dual UNION ALL SELECT 'PIPE ERW PE STPG370 12.7T 6M 250A MTR' FROM dual UNION ALL SELECT 'PIPE ERW PE STPG370 12.7T 6M 250A MTR' FROM dual ) SELECT pname , REGEXP_SUBSTR(pname, '[0-9.]+T') x FROM t ;