Search code examples
sqloracle-databasetrim

Replace Function for specific values

I have a table like this:

  1. Article
  2. Date
  3. Status
  4. Code

In the column "Status" are values like '00', '01', 'OB' In the column "Code" are values like:

  • [05]+000569
  • [1B] 5555
  • 690KB
  • -859

I am just interested for rows where "Status" = '00'. The status '00' shows just values like [05]+000569. These values should be trimmed with the following function:

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

How can I build a SELECT SQL with this function just for rows where "Status" = '00' ?

Solution

  • I see. You want a case expression:

    select (case when status = '00' then replace(regexp_substr(Code, '(^|[+])[0-9]+'), '+', '')
        end)