How to retrieve the last part (i.e. a number varies in length) of a text column of which’s length varies in Oracle and store this in another column?

I am trying to get the last number from a Oracle SQL column called Description and store it in another column called Thickness.

As you see, the length of the description varies as well as the length of the number at the very end. It is sometimes a double such as 1.5, sometimes an integer like 3. I have tried checking for ‘X’ at the end but it was not a success since RIGHT keyword is not valid in Oracle.

Thanks in advance for your help!

Answer

One option uses regexp_substr():

update mytable 
set thickness = regexp_substr(description, 'd+(.d+)?$')

Note that you could very well use a computed column rather than actually storing this derived information:

alter table mytable drop column thickness;

alter table mytable
add thickness as (to_number(regexp_substr(description, 'd+(.d+)?$')));

Demo on DB Fiddle