Get oracle view with end of value trimmed

And without regular expressions:

create or replace view DerivedDoc as
select
  case 
    when DocNo like '%\_AR%' escape '\' then 
      substr(DocNo, 1, instr(DocNo, '_AR') - 1) 
    else 
      DocNo
    end as DerivedDocNo
from 
  tb_doc

Select allOpen in new window

Do be aware that this assumes that the data is as you show in your example and that you don't mind anything being removed after the '_ARx'. You could substitute Alexander's regex into this as he did not originally include the create view statement, but did really answer main part of the question.

Edit: Escaped the _ character as suggested by Alexander below.

@Martyn: this will erase everything after "_AR" even if it is found in the middle of the string. I doubt this is helpful... He's on 11gR2, so he should use regular expressions for stuff like that!

@Alexander: I know, and my example specifies this in the description. The OP does not specify that they do not want this to occur and imply that the _ARx will be at the end in their data. With that in mind, it may well be helpful. I have also commented that the OP could use your regex example (due credit given) in the create or replace view statement, which was part of the original request.

Apart from that, your query is wrong: you have to escape the '_'. If you do not, this will count as a wildcard character (1 char)!

You may test your proposed solution with the following string:
Doc1____AR_____AR1
and you'll notice it will fail

Thank you for that suggestion. I have amended my answer accordingly.

@Sam,
Aside form the actual characters, how is this different from your previous question?

https://www.experts-exchange.com/questions/29121928/Oracle-Query-to-get-the-string-replaced-looking-a-pattern.html

Aside form the actual characters, how is this different from your previous question?

I thought this to myself ;-) It's virtually the same...