CODEWITHSUNDEEP
mysql
Dumitru Gutu
Dumitru Gutu

Reputation: 579

mysql regular replace characters with empty char

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:

1
1
2
2

EDIT

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

return me 

11

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

Upvotes: 0

Views: 82

Answers (1)

zedfoxus
zedfoxus

Reputation: 37069

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.

Upvotes: 1

Related Questions