Regex for specific pattern - String followed by numbers

Question

We're trying to use a REGEX expression inside MySQL. Say we have a 2-column table with 5 rows as follow:

1  marketing
2  marketing1
3  marketing12
4  office5
5  marketing44Tomorrow

I'd like to have a SELECT statement that returns: marketing, marketing1, marketing12. Meaning a string (marketing) followed by nothing or by a number only.

This statement:

select * from ddd
where column_name2 REGEXP 'marketing[0-9]'

doesn't work as it does not return "marketing" alone and it will return "marketing44Tomorrow".

Try it with MySQL regex word boundery [[:<:]] [[:>:]]

Raymond Nijland
– Raymond Nijland

2018-02-15 20:57:25 +00:00
Commented Feb 15, 2018 at 20:57 — Raymond Nijland
– Raymond Nijland, Commented Feb 15, 2018 at 20:57
Can you elaborate in my case?

Shahin Shadfar
– Shahin Shadfar

2018-02-15 21:18:02 +00:00
Commented Feb 15, 2018 at 21:18 — Shahin Shadfar
– Shahin Shadfar, Commented Feb 15, 2018 at 21:18

MJ.Noori · Accepted Answer · 2018-02-15 21:26:44Z

2

You can use : marketing([0-9]+)?[[:>:]]

`marketing` - any word start with **marketing**
`([0-9]+)` - any digit where....

 1. `?` - Maybe there may there not 
 2. `[[:>:]]` - Must be the last

Result:

SELECT * FROM ddd WHERE column_name2 REGEXP 'marketing([0-9]+)?[[:>:]]'

edited Feb 15, 2018 at 21:26

answered Feb 15, 2018 at 20:59

MJ.Noori

1,23713 silver badges23 bronze badges

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

3 Comments

Shahin Shadfar Over a year ago

Thank you. When I enter this exact expression in my SELECT, it returns nothing. The problem is in the "\b". Without it, it works but then it also returns the "marketing44Tomorrow". Can you tell me how to use this in MySQL?

MJ.Noori Over a year ago

you can use [[:>:]] Instead of \b

Shahin Shadfar Over a year ago

select * from ddd where column_name2 REGEXP 'marketing([0-9]+)?[[:>:]]' works. Looks like the equivalent of "\b" in MySQL is [[:>]].

vicky · Accepted Answer · 2018-02-15 21:01:12Z

1

try this, select * from ddd where column_name2 REGEXP 'marketing[0-9]$'

answered Feb 15, 2018 at 21:01

vicky

1892 silver badges9 bronze badges

1 Comment

Shahin Shadfar Over a year ago

This does not work as it does not return "marketing" alone and other issues.

Shahin Shadfar · Accepted Answer · 2018-02-15 21:26:19Z

0

As a conclusion, the perfect answer to my question in the MySQL context is:

SELECT * FROM ddd WHERE column_name2 REGEXP 'marketing([0-9]+)?[[:>:]]'

"MJN Belief" got it almost right up here.

answered Feb 15, 2018 at 21:26

Shahin Shadfar

133 bronze badges

Collectives™ on Stack Overflow

Regex for specific pattern - String followed by numbers

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related