Substring regex matching in mysql

Question

I've one complex question that been struggle me for couple hours and seeking help from mysql expert. :) Thank you in advanced.

Table : t1 ; Column: name

Given table:

name
-----
$abc|def|$cde
efd|$acd
$gcb|$bvv|ggg

Expected outcome (pull only the string without $ prefix, pipe indicates the field values separator):

name
-----
def
efd
ggg

-- Sql to create and insert
create table t1 (name varchar(100));
        insert into t1 (name) values ('$abc|def|$cde');
        insert into t1 (name) values ('efd|$acd');
        insert into t1 (name) values ('$gcb|$bvv|ggg');

Mysql version: 5.6.40

1) consider not storing pipe delimited data in your table, and 2) consider upgrading to MySQL 8+. This query will be ugly on MySQL 5. — Tim Biegeleisen, Nov 19 '20 at 08:22
What is MAX amount of separate "piped" values per column value? — Akina, Nov 19 '20 at 08:23

Tim Biegeleisen · Answer 1 · 2020-11-19T08:41:14.173

1

On MySQL 8+, you could try:

SELECT
    col,
    REGEXP_REPLACE(CONCAT('|', col, '|'), '^.*\\|([a-z]+)\\|.*$', '$1') AS col_out
FROM yourTable;

The idea here is to start with this slightly modified column value:

|$abc|def|$cde|

Then, we search for a letter-only sequence surrounded by pipes, and replace with that captured group.

Demo

edited Nov 19 '20 at 08:41

answered Nov 19 '20 at 08:19

Tim Biegeleisen

502,043
27
286
360

@Akina Thanks for that feedback, I initially answered on a cell phone and so couldn't do any testing. The issue is that metacharacters appear to require double escaping when calling `REGEXP_REPLACE`. The updated version is working now. – Tim Biegeleisen Nov 19 '20 at 08:41
If there is more than one subvalue without `$` then only last one will be returned... from the other side, OP does not show such values. – Akina Nov 19 '20 at 08:57
Now you're seeing edge cases which we don't even expect to be there. If there could be more than one match, then the OP absolutely should _normalize_ the data and cease storing it this way. One column value should generate one output value. – Tim Biegeleisen Nov 19 '20 at 08:58
Thanks Akina & Tim, thank you so much for the codes and feedback, and think beyond. It will be just one subvalue in the field (my fault, should clarify earlier). The code is clear and thank you so much for that, I hope. Unfortunately I have to still stick with the old mysql version until I really do hope we get a chance to upgrade this. – amine Nov 19 '20 at 09:52

Akina · Accepted Answer · 2020-11-19T10:18:55.133

1

SELECT DISTINCT
       name, SUBSTRING_INDEX(SUBSTRING_INDEX(t1.name, '|', num), '|', -1) one_value
FROM t1
/* max 3 subnames per name - expand if needed */
CROSS JOIN (SELECT 1 num UNION SELECT 2 UNION SELECT 3) numbers
HAVING one_value NOT LIKE '$%';

fiddle

edited Nov 19 '20 at 10:18

answered Nov 19 '20 at 08:26

Akina

39,301
5
14
25

@amine Consider accepting this answer in that case. – Tim Biegeleisen Nov 19 '20 at 09:54
Sorry, was taking sometimes to digest and playing with more subnames, I have appended a new entry $gcb|$bvv|$ggg|$ste|sgg & adding union more subnames in. The query result do return some duplicates. It that an expected behavior from this query, I can later pass them to distinct function to remove the dupes if that is expected behavior. https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=e5f926f1055cdd4d10aa25ac8369b923 :) – amine Nov 19 '20 at 10:14
@amine The simplest solution - `SELECT DISTINCT ...`. Updated. – Akina Nov 19 '20 at 10:17

Substring regex matching in mysql

2 Answers2

Demo