MySQL Forums
Forum List  »  Newbie

regexp_substr issue/bug?
Posted by: kkt vasile
Date: June 08, 2018 11:52AM

i have a simple test table with id integer,st varchar(50),sta varchar(50).
No i have inserted 4 rows like

mysql> select * from test;
+------+------------------------------+------+
| id | st | sta |
+------+------------------------------+------+
| 1 | a - 01 - bla bla | NULL |
| 2 | b - 02 - blsdsdf 0324 blasad | NULL |
| 3 | c - 03 - blsdsdf 0324 blasad | NULL |
| 4 | d - 12 - asdfsfg | NULL |
+------+------------------------------+------+

now this works ok:
mysql> select regexp_substr(st,'[0-9]{1,5}') from test;
+--------------------------------+
| regexp_substr(st,'[0-9]{1,5}') |
+--------------------------------+
| 01 |
| 02 |
| 03 |
| 12 |
+--------------------------------+

Please notice the leading zero

But this works different:

mysql> update test set sta=regexp_substr(st,'[0-9]{1,5}');
Query OK, 4 rows affected (0.11 sec)
Rows matched: 4 Changed: 4 Warnings: 0

mysql> select * from test;
+------+------------------------------+------+
| id | st | sta |
+------+------------------------------+------+
| 1 | a - 01 - bla bla | 1 |
| 2 | b - 02 - blsdsdf 0324 blasad | 2 |
| 3 | c - 03 - blsdsdf 0324 blasad | 3 |
| 4 | d - 12 - asdfsfg | 12 |
+------+------------------------------+------+
4 rows in set (0.00 sec)

notice the missing zero
Am I doing something wrong?
is this a bug?

Is there a way to retrieve the second matching 0324?

P.S.: this looks nice while writing but after i post it is looks so ugly

Options: ReplyQuote


Subject
Written By
Posted
regexp_substr issue/bug?
June 08, 2018 11:52AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.