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? — miken32
– miken32, Commented Dec 15, 2015 at 5:04

zedfoxus · Accepted Answer · 2015-12-15 15:46:29Z

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.

Collectives™ on Stack Overflow

mysql regular replace characters with empty char

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related