How to remove leftmost group of numbers from string in Oracle SQL?

Question

I have a string like T_44B56T4 that I'd like to make T_B56T4. I can't use positional logic because the string could instead be TE_2BMT that I'd like to make TE_BMT.

What is the most concise Oracle SQL logic to remove the leftmost grouping on consecutive numbers from the string?

EDIT: regex_replace is unavailable but I have LTRIM,REPLACE,SUBSTR, etc.

question from:https://stackoverflow.com/questions/65910032/how-to-remove-leftmost-group-of-numbers-from-string-in-oracle-sql

Answer 1

It seems that you must use standard string functions, as regular expression functions are not available to you. (Comment under Gordon Linoff's answer; it would help if you would add the same at the bottom of your original question, marked clearly as EDIT).

Also, it seems that the input will always have at least one underscore, and any digits that must be removed will always be immediately after the first underscore.

If so, here is one way you could solve it:

select s, substr(s, 1, instr(s, '_')) || 
          ltrim(substr(s, instr(s, '_') + 1), '0123456789') as result
from (
    select 'T_44B56T4' s from dual union all 
    select 'TXM_1JK7B'   from dual union all
    select '34_AB3_1D'   from dual
) 

S         RESULT            
--------- ------------------
T_44B56T4 T_B56T4           
TXM_1JK7B TXM_JK7B          
34_AB3_1D 34_AB3_1D  

I added one more test string, to show that only digits immediately following the first underscore are removed; any other digits are left unchanged.

Note that this solution would very likely be faster than regexp solutions, too (assuming that matters; sometimes it does, but often it doesn't).