CODEWITHSUNDEEP
mysqlregexreplace
Bujji
Bujji

Reputation: 1737

mysql replace regular expression what ever in the braces in a string

Below are the few rows of a column data in my mysql database

Data

test(victoryyyyy)king

java(vaaaarrrryy)side

(vkittt)sap

flex(vuuuuu)

k(vhhhhyyy)kk(abcd)k

In all rows there is random string that starts with

(v

and ends with

)

My task :- I have to replace all string from '(v' to ')' with empty space ( that is ' ') I shouldn't touch other values in the braces , in the above case I should not replace (abcd) in the last row

I mean for the above example the result should be 

test king

java side

 sap

flex 

kkk(abcd)k

Could any one please help me ?

Thank You

Regards

Kiran

Upvotes: 0

Views: 446

Answers (2)

Bujji
Bujji

Reputation: 1737

I wrote my own function for this and it is working . Thanks every one .

drop FUNCTION  replace_v;

CREATE FUNCTION replace_v (village varchar(100)) RETURNS varchar(100)
DETERMINISTIC

   BEGIN  
      DECLARE a INT Default 0 ;
      DECLARE lengthofstring INT Default 0 ;
      DECLARE returnString varchar(100) Default '' ;
      DECLARE charString varchar(100) Default '' ;
      DECLARE found       char(1) default 'N';
      declare seccharString varchar(100) Default '' ;
      set lengthofstring = length(village); 

  simple_loop: LOOP
         SET a=a+1;
         set charString=substr(village,a,1);
         if(charString = '(') then
           set seccharString=substr(village,a+1,1);
           if( seccharString = 'v' || seccharString = 'V' || seccharString = 'p' || seccharString = 'P'
           || seccharString = 'm' || seccharString = 'M' ) then
               set found='y';
            end if;
          end if ;      

          if(found='n') then
            set returnString = concat (returnString,charString);
          end if;  

         if(charString = ')') then 
            set found='n';
          end if ;            

          IF a>=lengthofstring THEN
              LEAVE simple_loop;
          END IF;
       END LOOP simple_loop;

       if ( found = 'y') then
         set returnString =village;
        end if;  

         RETURN (replace( returnString,'&', ' '));
END

Upvotes: 0

zerkms
zerkms

Reputation: 255105

Mysql doesn't support regexes for replace tasks.

So you can only use string functions to find and substr necessary part.

Upvotes: 1

Related Questions