I would like to remove 0 after decimal in string field:

Start:

Expected result:

i used this for the moment:

TRIM(CONCAT(CAST(Regexp_extract(size,r'^(?:[^\s]*\s){0}([^\s]*)\s?') AS DECIMAL),'',Regexp_extract(size,r'^(?:[^\s]*\s){1}([^\s]*)\s?'))) as size

Is there an easier way to do it?

Thanks in advance for your help

CodePudding user response：

Consider this:

SELECT REGEXP_REPLACE(str, r'\.?0*\s', ' ') 
  FROM UNNEST(['60.0 ML', '45.50 S', '450.00 LL']) str

CodePudding user response：

You can try this solution:

SELECT 
  CONCAT(
    RTRIM(RTRIM(SPLIT(size, ' ')[OFFSET(0)], '0'), '.'),
    ' ',
    SPLIT(size, ' ')[OFFSET(1)]
  ) AS size
FROM yourtable;