regular expression search and replace in mysql

Question

How can we achieve this in a single sql (mysql) query ?

I need to search some patterns in a column and if any pattern matches then replace that pattern with a specific string. like if

   pattern ptr1 matches then replace this with str1
   pattern ptr2 matches then replace this with str2

I need a function like replace that can replace a regular expression. here is my query i need to improve this for regular expression

UPDATE category SET column1 = 
(
    CASE
    WHEN (column1 REGEXP 'xy') THEN REPLACE(column1, 'xy' , 'ffff')
    ELSE column1
    END
)

Please help me in this.

Thanks I tried that function but did not work. its working this way when i am passing a string like UPDATE category SET column1 = (CASE WHEN (column1 REGEXP '^xy') THEN regex_replace ('^xy' , 'ffff','kunal pawar') ELSE column1 END) but did not work when i pass column name like - UPDATE category SET column1 = (CASE WHEN (column1 REGEXP '^xy') THEN regex_replace ('^xy' , 'ffff',column1) ELSE column1 END) — kunal, Jun 03 '13 at 08:04
If you solved the problem, go ahead and answer your own question. — Roger, Jun 03 '13 at 08:10

score 0 · Answer 1 · answered Jun 03 '13 at 07:48

0

you can put multiple replace function while updating table values -

UPDATE category SET column1 = 
REPLACE(REPLACE(column1, 
                ptr1, 
                str1),
        ptr2,
        str2)

answered Jun 03 '13 at 07:48

pratik garg

actually my problem is like that - suppose i have a column `name` and then if i get kunal# in name then kunal# should be replaced by pawar and if i find kunal@ in the name then it should be replaced by pawar-1 for ex hidfds kunal# 434fdwef -> hidfds pawar 434fdwef hidfds kunal@ 434fdwef -> hidfds pawar-1 434fdwef – kunal Jun 03 '13 at 08:09

1 Answers1