[MDEV-26958] Find second occurence with REGEXP_SUBSTR Created: 2021-11-02  Updated: 2021-11-05

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Minor
Reporter: Tadas Balaišis Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 Description   

I wonder, how can I find second/third occurrence with REGEXP_SUBSTR like I'm able to do in Oracle?

 Comments   
Comment by Tadas Balaišis [ 2021-11-03 ]

I failed to find suitable regex expression for that. For now I found a workaround with substring_index (for comma separated string):

select case when LENGTH(l)-LENGTH(REPLACE(l,',',\N))>=3-1 then substring_index(substring_index(l,',',3),',',-1) END p FROM (SELECT '0,2,102,-5' l FROM DUAL)

Comment by Sergei Golubchik [ 2021-11-04 ]

As a workaround, you can do it with two regexp_substr or with regex_replace, like

MariaDB [test]> select regexp_replace(l, '([^,]+,){0}([^,]+).*', '\\2') from (select '0,2,102,-5' l) x;
 
+--------------------------------------------------+
 
| regexp_replace(l, '([^,]+,){0}([^,]+).*', '\\2') |
 
+--------------------------------------------------+
 
| 0                                                |
 
+--------------------------------------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> select regexp_replace(l, '([^,]+,){2}([^,]+).*', '\\2') from (select '0,2,102,-5' l) x;
 
+--------------------------------------------------+
 
| regexp_replace(l, '([^,]+,){2}([^,]+).*', '\\2') |
 
+--------------------------------------------------+
 
| 102                                              |
 
+--------------------------------------------------+
 
1 row in set (0.001 sec)

Note the number in curly braces {2} specifies the value you want.

Comment by Tadas Balaišis [ 2021-11-05 ]

Excellent. Thanks.

Generated at Thu Feb 08 09:49:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.