MySQL 8.0

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

select version();


version()


8.0.30


CREATE TABLE test
(
  measure VARCHAR (250) NOT NULL
);
INSERT INTO test VALUES
('75cl'),
('70im'),
('7im'),
('30cim'),
('30clim');
SELECT * FROM test;


measure


75cl

70im

7im

30cim

30clim


 SUBSTR(str FROM pos FOR len)
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUBSTR(str FROM pos FOR len)' at line 1

--
-- MySQL code
--

SELECT
  measure AS m,
  REGEXP_SUBSTR(measure, '^[0-9]+') AS num,
  REGEXP_SUBSTR(measure, '^[0-9]+cl|^[0-9]+') AS rex_str


--  LENGTH(CONCAT('xyz', REGEXP_SUBSTR(measure, '^[0-9]+'))) AS strlen,
 
--  (LENGTH(CONCAT('xyz', REGEXP_SUBSTR(measure, '^[0-9]+'))) - 2)
 
--  SUBSTR
--  (
--    (CONCAT('xyz', REGEXP_SUBSTR(measure, '^[0-9]+'))) FROM
--    (LENGTH(CONCAT('xyz', REGEXP_SUBSTR(measure, '^[0-9]+'))) - 2) FOR 2
--  )

FROM                                                                
  test;


m
num
rex_str


75cl
75
75cl

70im
70
70

7im
7
7

30cim
30
30

30clim
30
30cl


--
-- PostgreSQL code
--

SELECT 
  measure,
  SUBSTRING(measure FROM '^[0-9]+cl|^[0-9]+') AS sstr,

  SUBSTRING(measure FROM '^[0-9]+') ||
  CASE
    WHEN SUBSTRING(measure FROM (LENGTH(SUBSTRING(measure FROM '^[0-9]+')) + 1)  FOR 2) = 'cl'
      THEN 'cl'
    ELSE
      ''
  END AS case_str
  
FROM
  test;


measure
sstr
case_str


75cl
  
0

70im
  
0

7im
  
0

30cim
  
0

30clim
  
0

`m`	`num`	`rex_str`
`75cl`	`75`	`75cl`
`70im`	`70`	`70`
`7im`	`7`	`7`
`30cim`	`30`	`30`
`30clim`	`30`	`30cl`

`measure`	`sstr`	`case_str`
`75cl`		`0`
`70im`		`0`
`7im`		`0`
`30cim`		`0`
`30clim`		`0`