MySQL Regex in middle of a string

Question

I am looking to run an SQL query using a regex match in the middle of a string.

For example, I have a string that is

Status Changed from Needs Action to Completed

I want to search where column = Status Changed from {REGEX} to Completed

Is this possible at all? I don't know regex too well so am seeking some help

What is the regexp that you want to match in the middle? Or do you mean that it can be anything in that place? — Barmar
– Barmar, Commented Sep 9, 2019 at 16:53
You can put parentheses around a pattern in the middle: WHERE column RLIKE 'Status Changed from (Needs Action|Incomplete) to Completed' — Barmar
– Barmar, Commented Sep 9, 2019 at 16:57
@charlie yes... but put a plus after it like so [a-zA-Z0-9]+ — Salman Arshad
– Salman Arshad, Commented Sep 9, 2019 at 18:16

GMB · Accepted Answer · 2019-09-09 21:52:47Z

1

From your requirement and the comments, it looks like the LIKE operator should be pretty close to what you need:

SELECT *
FROM mytable
WHERE mycolumn LIKE 'Status Changed from % to Completed'

This will allow any sequence of characters as the previous status.

Else, if you know in advance the list of possible status values, you can make the filtering more accurate with a regexp:

SELECT *
FROM mytable
WHERE mycolumn REGEXP 'Status Changed from (Needs Action)|(Open) to Completed'

The REGEXP operator checks whether values in the column match the given regular expression. The pipe (|) stands for the alternation operator (at least one of the value must match).

Demo on DB Fiddle

create table mytable (
  mycolumn varchar(500)
);

insert into mytable values 
    ('Status Changed from Needs Action to Completed'),
    ('Status Changed from Open to Completed'),
    ('Not a relevant record');

Query #1

SELECT *
FROM mytable
WHERE mycolumn LIKE 'Status Changed from % to Completed';

| mycolumn                                      |
| --------------------------------------------- |
| Status Changed from Needs Action to Completed |
| Status Changed from Open to Completed         |

Query #2

SELECT *
FROM mytable
WHERE mycolumn REGEXP 'Status Changed from (Needs Action)|(Open) to Completed';

| mycolumn                                      |
| --------------------------------------------- |
| Status Changed from Needs Action to Completed |
| Status Changed from Open to Completed         |

edited Sep 9, 2019 at 21:52

answered Sep 9, 2019 at 20:22

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

charlie Over a year ago

i tried using 'Status Changed from % to Completed' but it returns no results

GMB Over a year ago

@charlie: this works fine with your sample data though. I added a DB fiddle to my answer for your reference.

Collectives™ on Stack Overflow

MySQL Regex in middle of a string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related