mysql regular replace characters with empty char

Question

I have the following strings in the table:

step1.cards.choice_step_1
step1.cards.choice_step_2

step2.cards.choice_step_1
step2.cards.choice_step_2

I would replace with empty characters all in these string using that pattern:

^step([0-9]|[1-9][0-9]).cards.choice_step_1$
^step([0-9]|[1-9][0-9]).cards.choice_step_2$

Does it possible to do that in MySQL?

Finally I would have in result:

EDIT

select regex_replace('[^0-9]','','step1.cards.choice_step_1');

return me

please help on correct pattern on the following data :

sometext.step1
sometext.step1.cards.choice_step_1
sometext.step1.cards.choice_step_2
sometext.step1.desire

sometext.step2
sometext.step2.cards.choice_step_1
sometext.step2.cards.choice_step_2
sometext.step2.desire

to get the following result:

step1
step1
step1
step1

step2
step2
step2
step2

Possible duplicate of [How to do a regular expression replace in MySQL?](http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql) — miken32, Dec 15 '15 at 05:04

score 1 · Accepted Answer · answered Dec 15 '15 at 15:46

You could try something like this:

create table test (field1 varchar(100));
select * from test;
+------------------------------------+
| field1                             |
+------------------------------------+
| sometext.step1                     |
| sometext.step1.cards.choice_step_1 |
| sometext.step1.cards.choice_step_2 |
| sometext.step1.desire              |
| sometext.step2                     |
| sometext.step2.cards.choice_step_1 |
| sometext.step2.cards.choice_step_2 |
| sometext.step2.desire              |
| step1                              |
+------------------------------------+

Query:

select 
    field1,

    -- find position of step
    position('step' in field1) as step,

    -- find position of dot AFTER step is found
    position('.' in 
            substr(field1, position('step' in field1), length(field1))) 
            + position('step' in field1) as dot,

    -- if position of 'step' --and-- position of 'step' + position of 'dot' is 1
    --    that means: step is at position 1 and dot is not found, display field as is
    -- if position of 'step' --and-- position of 'step' + position of 'dot' are equal
    --    that means: dot is not found. Grab everything from step onwards
    -- if position of 'step' --and-- position of 'step' + position of 'dot' are **NOT** equal
    -- grab everything from where step was found thruogh just before dot was found
    case
    when position('step' in field1) = 1 
         and 
         position('.' in substr(field1, position('step' in field1), length(field1))) 
         + position('step' in field1) = 1 
         then 
            field1
    when position('step' in field1) = 
        position('.' in substr(field1, position('step' in field1), length(field1))) 
        + position('step' in field1) 
        then 
            substr(field1, position('step' in field1), length(field1))
    else 
        substr(field1, 
            position('step' in field1), 
            position('.' in substr(field1, position('step' in field1), length(field1)))-1
        )
    end as ans  

from test;

Result:

+------------------------------------+------+------+-------+
| field1                             | step | dot  | ans   |
+------------------------------------+------+------+-------+
| sometext.step1                     |   10 |   10 | step1 |
| sometext.step1.cards.choice_step_1 |   10 |   16 | step1 |
| sometext.step1.cards.choice_step_2 |   10 |   16 | step1 |
| sometext.step1.desire              |   10 |   16 | step1 |
| sometext.step2                     |   10 |   10 | step2 |
| sometext.step2.cards.choice_step_1 |   10 |   16 | step2 |
| sometext.step2.cards.choice_step_2 |   10 |   16 | step2 |
| sometext.step2.desire              |   10 |   16 | step2 |
| step1                              |    1 |    1 | step1 |
+------------------------------------+------+------+-------+

Notice the last field that has the data you desire. Please feel free to tweak this to your needs.

mysql regular replace characters with empty char

1 Answers1