Search code examples
sqloracle-databasesubstringsubstr

get everything before a string including itself oracle

I need to get everything before a string including itself and replace it with something else after that. For example, if I have a value in column as 28/29/81/732536/1496071 then I want to select everything before 81 including itself, i.e I want 28/29/81 from it and replace it with some other string. I have tried the below, but I am getting only 28/29.

 SELECT SUBSTR(eda.ATTRIBUTE_VALUE, 0, INSTR(eda.ATTRIBUTE_VALUE, '81')-2) AS output, ATTRIBUTE_VALUE
FROM EVENT_DYNAMIC_ATTRIBUTE eda

enter image description here

Solution

  • The solution will have to work when the "token" ( the '81' in your example ) appears between two slashes, or right at the beginning of the string and before a slash, or right after the last slash at the end of the string. It should not match if '81' appears as part of a "token" (between slashes or before the first or after the last slash). Also, if the "token" appears more than once, it should be replaced (with everything before it) only once, and if it doesn't appear at all, then the original string should be unchanged.

    If these are the rules, then you can do something like I show below. If any of the rules are different, the solution can be modified to accommodate.

    I created a few input strings to test all these cases in a WHERE clause. I also created the "search token" and the "replacement text" in a second subquery in the WITH clause. The entire WITH clause should be replaced - it is not part of the solution, it is only for my own testing. In the main query you should use your actual table and column names (and/or hardcoded text).

    I use REGEXP_REPLACE to find the token and replace it and everything that comes before it (but not the slash after it, if there is one) with the replacement text. I must be careful with that slash after the search token; I use a backreference in the replacement string in REGEXP_REPLACE for that purpose.

    with
  event_dynamic_attribute ( attribute_value ) as (
    select '28/29/81/732536/1496071' from dual union all
    select '29/33/530813/340042/88'  from dual union all
    select '81/6883/3902/81/993'     from dual union all
    select '123/45/6789/81'          from dual
  ),
  substitution ( token, replacement ) as (
    select '81', 'mathguy is great'  from dual
  )
select attribute_value,
       regexp_replace (attribute_value, '(^|.*?/)' || token || '(/|$)',
                                        replacement || '\2', 1, 1) new_attrib_value
from   event_dynamic_attribute cross join substitution
;

ATTRIBUTE_VALUE         NEW_ATTRIB_VALUE                       
----------------------- ----------------------------------------
28/29/81/732536/1496071 mathguy is great/732536/1496071         
29/33/530813/340042/88  29/33/530813/340042/88                  
81/6883/3902/81/993     mathguy is great/6883/3902/81/993       
123/45/6789/81          mathguy is great