CODEWITHSUNDEEP
sqloracle-database
user15746603
user15746603

Reputation:

Ltrim trimming extra character

I have the below code:

SELECT
    ltrim('REASON_ACTIVE_DCA', 'REASON_') reason
FROM
    dual

However, I'm obtaining '_CTIVE_DCA'. What's happening and how can I get 'ACTIVE_DCA' with ltrim?

Upvotes: 0

Views: 441

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Because LTRIM() removes all the characters as a set. So all leading "R"s and "E"s and so on removed. In fact, the ordering of the characters in the second string is irrelevant, so you would get the same result with '_NOSAER'.

If you want to remove the leading string of REASON_ -- if present -- then you don't use trim(). Instead, one method is:

select (case when 'REASON_ACTIVE_DCA' LIKE 'REASON$_%' ESCAPE '$'
             then substr('REASON_ACTIVE_DCA', 8)
             else 'REASON_ACTIVE_DCA'
        end)

There are other ways, such as:

select regexp_replace('REASON_ACTIVE_DCA', '^REASON_', '')

Upvotes: 2

user5683823
user5683823

Reputation:

I would do it with regular string functions (not regular expressions), and using INSTR instead of LIKE so I don't have to worry about escaping underscore.

Something like this - including a few sample strings in the WITH clause for testing:

with
  inputs (i_str) as (
    select 'REASON_ACTIVE_DCA' from dual union all
    select 'REASON_NOT_GIVEN'  from dual union all
    select null                from dual union all
    select 'REASON-SPECIAL'    from dual union all
    select 'REASON_'           from dual union all
    select 'REASON'            from dual
  )
select i_str, substr(i_str, case instr(i_str, 'REASON_') 
                                 when 1 then 1 + length('REASON_')
                                 else        1   end) as new_str
from   inputs;

I_STR             NEW_STR          
----------------- -----------------
REASON_ACTIVE_DCA ACTIVE_DCA       
REASON_NOT_GIVEN  NOT_GIVEN        
                                   
REASON-SPECIAL    REASON-SPECIAL   
REASON_                            
REASON            REASON

Upvotes: 2

Related Questions