CODEWITHSUNDEEP
sqloracle-databasetrim
Aaron
Aaron

Reputation: 331

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:

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' ?

Upvotes: 0

Views: 25

Answers (2)

Littlefoot
Littlefoot

Reputation: 142753

"Trim" for status = '00', don't for other statuses:

SELECT CASE
          WHEN status = '00'                                              --> trim
          THEN                                                            --> for
             REPLACE (REGEXP_SUBSTR (Code, '(^|[+])[0-9]+'), '+', '')     --> '00'
          ELSE
             code                                                         --> else, do nothing
       END
          AS code
  FROM your_table

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269913

I see. You want a case expression:

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

Upvotes: 1

Related Questions