Search code examples
sqloracletrim

Trim specific string with leading zeros

I am operating on an oracle server with a table that contains one really weird column. This column contains strings like:

  • [0X]+00000026
  • [22]+2222,555,6666
  • [WRI] 0000,00
  • FKI
  • 555

Every case has its own structure. Now I would like to transform the first example to '26'. The second one I would like to transform to '2222'. The last one to '555'.

How would you build that? Have you ever seen something similar?

Best Regards

Solution

  • I think this does what you want:

    select replace(regexp_substr(str, '(^|[+])[0-9]+'), '+', '')

    Here is a db<>fiddle.