Forum Stats

  • 3,781,574 Users
  • 2,254,530 Discussions
  • 7,879,757 Comments

Discussions

Function to replace using Regex

Sabegh
Sabegh Member Posts: 34 Red Ribbon
edited Jun 12, 2020 1:27PM in SQL & PL/SQL

Hello,

One of our application is storing data with some special characters with it i.e., {Firewall__bRule__bRequest, TV__as, ImageNow__b6__d5}. I want to have a function which will take this string and remove/replace these special characters {'__[abdfmpPu]{1}')}, if none of these present, return original string. Special Character will always begin with 2 underscore (__).

  • if string contains __a, replace with '
  • if string contains __b or __u, replace with single space
  • if string contains __d, replace with .
  • if string contains __f, replace with /
  • if string contains __P, replace with (
  • if string contains __p, replace with )

I wrote a function for this, but was wondering if there is easy way to do this, without using WHILE LOOP.

CREATE OR REPLACE

FUNCTION ACI_REPLACE_FP_SYMBOLS ( IN_STRING  IN   VARCHAR2 )

RETURN     varchar2

IS

RETURN_STRING VARCHAR2(32767);

V_STRING      VARCHAR2(32767) := TRIM(IN_STRING);

O_STRING      VARCHAR2(32767) := TRIM(IN_STRING);

L_STRING      VARCHAR2(32767) ;

BEGIN

        WHILE  REGEXP_SUBSTR (V_STRING,'__[abdfmpPu]{1}') IS NOT NULL

        loop

       

        CASE

        WHEN REGEXP_SUBSTR (V_STRING,'__u|__b') IS NOT NULL THEN

        L_STRING := REGEXP_REPLACE(REGEXP_REPLACE (V_STRING,'__b|__u',' '), '\s{2,}', ' ') ;

       

        WHEN REGEXP_SUBSTR (V_STRING,'__d') IS NOT NULL THEN

        L_STRING := REGEXP_REPLACE (V_STRING,'__d', '.');

       

        WHEN REGEXP_SUBSTR (V_STRING,'__a') IS NOT NULL THEN

        L_STRING := REGEXP_REPLACE (V_STRING,'__a','''');

       

        WHEN REGEXP_SUBSTR (V_STRING,'__f') IS NOT NULL THEN

        L_STRING := REGEXP_REPLACE (V_STRING,'__f','/');

       

        WHEN REGEXP_SUBSTR (V_STRING,'__m') IS NOT NULL THEN

        L_STRING := REGEXP_REPLACE (V_STRING,'__m','@');

       

        WHEN REGEXP_SUBSTR (V_STRING,'__P') IS NOT NULL THEN

        L_STRING := REGEXP_REPLACE (V_STRING,'__P', '(' );

       

        WHEN REGEXP_SUBSTR (V_STRING,'__p') IS NOT NULL THEN

        L_STRING := REGEXP_REPLACE (V_STRING,'__p', ')' );

       

        ELSE V_STRING := L_STRING ;

        END CASE;

       

        V_STRING := L_STRING;

       

        END LOOP;

 

  RETURN_STRING := V_STRING;

RETURN RETURN_STRING;

    

END;

Sample data:

The string TV__s does't have any special character, so should be returned as it is.

SELECT

ACI_REPLACE_SYMBOL('Agent__Ps__p__bError') COL1,

ACI_REPLACE_SYMBOL('Reporting__fBI') COL2,

ACI_REPLACE_SYMBOL('TV__as') COL3,

ACI_REPLACE_SYMBOL ('TV__s') COLW, -- this is not working should return 'TV__s'

ACI_REPLACE_SYMBOL('ImageNow__b6__d5') COL4,

ACI_REPLACE_SYMBOL('External__bWebsite__b__Pcoal__dcom__p') COL5,

ACI_REPLACE_SYMBOL('I__dS__dP__dO__d__bSystem') COL6,

ACI_REPLACE_SYMBOL('__m__bRisk') COL7,

ACI_REPLACE_SYMBOL('G__fL') COL8,

ACI_REPLACE_SYMBOL('Communicator__b__POCS__p') col9

FROM DUAL;

pastedImage_0.png

Tagged:
RanagalFrank Kulash

Best Answer

  • mathguy
    mathguy Member Posts: 10,221 Blue Diamond
    edited Oct 21, 2019 8:12PM Accepted Answer
    Sabegh wrote:Yes you are correct I need spaces between the words, also if string contains __b__u, there should be one space only.Since there aren't many substitution pairs, hard-coding will do just fine.I was just testing it with REGEXP, that's why used it, no need to use if it can be done in another efficient/simple manner.

    OK - I saw this only after I posted my answer.

    You can easily substitute a space for __b and __u (instead of NULL in my answer). Since you may also have the combination  "__b " in your input (meaning, __b followed by an actual space), and since you probably also only want one space in that case, it would likely be best to take care of changing multiple spaces to a single one at the very end (and likely with a regexp_replace). I assume you can write the code for that yourself, but please do speak up if you need help.

Answers

  • mathguy
    mathguy Member Posts: 10,221 Blue Diamond
    edited Oct 21, 2019 7:56PM

    Do you really want to completely remove __b and __u?  In your example you have Firewall__bRule__bRequest - this will become FirewallRuleRequest, don't you need spaces between the words?

    Were do you store the substitution pairs? Do you have a small table, or perhaps a small view - or do you need to hard-code everything?

    And why REGEXP? More likely you will get an efficient solution with the standard string function REPLACE, not with REGEXP_REPLACE.

  • Sabegh
    Sabegh Member Posts: 34 Red Ribbon
    edited Oct 21, 2019 8:06PM

    Yes you are correct I need spaces between the words, also if string contains __b__u, there should be one space only.

    Since there aren't many substitution pairs, hard-coding will do just fine.

    I was just testing it with REGEXP, that's why used it, no need to use if it can be done in another efficient/simple manner.

  • mathguy
    mathguy Member Posts: 10,221 Blue Diamond
    edited Oct 21, 2019 8:09PM

    Here is one way - using only the REPLACE function (which should make the function faster) and hard-coding the substitution table in the function body (very likely not the most efficient arrangement).

    create or replace function aci_replace_fp_symbols ( in_string  in  varchar2 )  return varchar2is  new_string varchar2(4000) := in_string;begin  for c in (    select '__a' as to_be_replaced, '''' as replacement from dual union all    select '__b'                  , null                from dual union all    select '__u'                  , null                from dual union all    select '__d'                  , '.'                 from dual union all    select '__f'                  , '/'                 from dual union all    select '__m'                  , '@'                 from dual union all    select '__P'                  , '('                 from dual union all    select '__p'                  , ')'                 from dual  )  loop    new_string := replace(new_string, c.to_be_replaced, c.replacement);  end loop;   return new_string;end;/select aci_replace_fp_symbols('{Firewall__bRule__bRequest, TV__as, ImageNow__b6__d5}') as new_string from dual;NEW_STRING                             -----------------------------------------{FirewallRuleRequest, TV's, ImageNow6.5}
    Ranagal
  • mathguy
    mathguy Member Posts: 10,221 Blue Diamond
    edited Oct 21, 2019 8:12PM Accepted Answer
    Sabegh wrote:Yes you are correct I need spaces between the words, also if string contains __b__u, there should be one space only.Since there aren't many substitution pairs, hard-coding will do just fine.I was just testing it with REGEXP, that's why used it, no need to use if it can be done in another efficient/simple manner.

    OK - I saw this only after I posted my answer.

    You can easily substitute a space for __b and __u (instead of NULL in my answer). Since you may also have the combination  "__b " in your input (meaning, __b followed by an actual space), and since you probably also only want one space in that case, it would likely be best to take care of changing multiple spaces to a single one at the very end (and likely with a regexp_replace). I assume you can write the code for that yourself, but please do speak up if you need help.

  • Sabegh
    Sabegh Member Posts: 34 Red Ribbon
    edited Oct 22, 2019 10:15AM

    Thanks Mathguy!

    Below is the modified code to take account double spaces. Let me test this out on entire data set this week, then I'll mark this as ANSWERED. Thanks for your help again!

    create or replace

    function  ACI_REPLACE_FP_SYMBOLS ( in_string  in  varchar2 )

      return varchar2

    is

      new_string varchar2(4000) := in_string;

    begin

      for c in (

        select '__a' as to_be_replaced, '''' as replacement from dual union all

        select '__b'                  , ' '                from dual union all

        select '__u'                  , ' '                from dual union all

        select '__d'                  , '.'                from dual union all

        select '__f'                  , '/'                from dual union all

        select '__m'                  , '@'                from dual union all

        select '__P'                  , '('                from dual union all

        select '__p'                  , ')'                from dual

      )

      LOOP

        NEW_STRING := REPLACE(NEW_STRING, C.TO_BE_REPLACED, C.REPLACEMENT);

       

      END LOOP;

     

      -- Replace 2 or more Spaces with single space

      new_string :=  REGEXP_REPLACE (NEW_STRING, '\s{2,}', ' ') ;

      return new_string;

    end;

  • Paulzip
    Paulzip Member Posts: 8,539 Blue Diamond
    edited Oct 31, 2019 5:56PM

    So you get a lot of good advice on how to solve your problem, then mark your own post as correct.... Sigh.

  • Sabegh
    Sabegh Member Posts: 34 Red Ribbon
    edited Nov 4, 2019 5:39PM

    It was MARKED answered long back. Are you not seeing it?

  • Paulzip
    Paulzip Member Posts: 8,539 Blue Diamond
    edited Nov 4, 2019 5:59PM

    Re-read what I wrote. My point was you got help but marked your OWN post correct. When you did that is irrelevant.

    Frank Kulash