It is necessary to take the value of X from the table Y X in the format 12345_пример . At the output you need to get the value of "пример" . The number of characters is arbitrary, only the separator "_" is known.

Could give birth only:

 select case instr(NAME,'_') when 0 then NAME else substr(NAME,0,instr(NAME,'_') -1) end from AONAME

What returns a part to a separator "_" . How to get part after separator?

  • substr(NAME, instr(NAME,'_')) or substr(NAME, instr(NAME,'_') + 1) . - Visman
  • @Visman Please post your comment as a response. - Nicolas Chabanovsky ♦
  • @NicolasChabanovsky, I'm not sure which option will give the correct result. - Visman
  • @Visman I think the answer is better than a comment anyway! - Nicolas Chabanovsky ♦

2 answers 2

Replace

 substr(NAME,0,instr(NAME,'_') -1)

on

 substr(NAME,instr(NAME,'_')+1)

Only the starting position for the selection of the substring is indicated here and the length of the selection is not indicated, so the characters are inserted into the substring until the end of the original string.

PS In both functions (substr, instr), the starting position is 1, not 0.

    If the substr format is violated, it is possible that the result is not exactly expected.
    For example for: '123 _' , returns null . Try the following:

     select regexp_replace (name, '^\d+_(\w+)$', '\1') from (select '123_abcde' name from dual);

    So the value of the field will remain unchanged, unless there is a full match on the mask.