mysql regex_replace : how to use regex group in replacement

Question

Have a table:

| id |      Name       |
|----------------------|
| 1  |test1 test1 test1|
| 2  |test2 test2 test2|

Want to select 1st regex group (used 1st word to simplify), so my query:

SELECT regexp_replace(name, "^([[:alnum:]]+)[[:space:]].*$","$1") FROM table;

Result:

|test1      |
|test1test2 |

So how it comes that result is accumulated? And how to avoid it and still be able to use regex group?

UPD: Mysql version: 8.0.11, MySQL Community Server - GPL

If you need to remove all after the first whitespace, why not use regexp_replace(name, "(?s)\\s.*","")? If there must be at least 1 letter or digit before, use regexp_replace(name, "(?s)(?<=[[:alnum:]])\\s.*",""). Also, are you sure the replacement backreference syntax is a dollar+number? Try "\\1". — Wiktor Stribiżew
– Wiktor Stribiżew, Commented May 9, 2018 at 7:36
@WiktorStribiżew, that's just an example, question in "HOW TO USE REGEX GROUP" in replacement, and I've tried \\1 it's just treated as "1". But to add with "1" result will be "1" for 1st row, and "11" for second! it's also accumulated, and I don't get why :( — 2oppin
– 2oppin, Commented May 9, 2018 at 7:54
I admit this is not expected and is possibly a bug, but what version of MySQL are you using? I understand it is MySQL 8.x, but maybe it is a test build? — Wiktor Stribiżew
– Wiktor Stribiżew, Commented May 9, 2018 at 8:00

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

https://bugs.mysql.com/bug.php?id=90803 - May 21:

Fixed in 8.0.12.

REGEXP_RELACE() results from one result set row could carry forward to the next row, resulting in accumulation of previous results in the current row.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 23, 2018 at 22:41

Rick James

144k15 gold badges144 silver badges255 bronze badges

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

2 Comments

2oppin Over a year ago

yupp, it should to be, had no chance to check yet

2oppin Over a year ago

seems 8.0.12 still not released, have you tried to install it?

Collectives™ on Stack Overflow

mysql regex_replace : how to use regex group in replacement

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related