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
– Tim Biegeleisen, Commented Nov 19, 2020 at 8:22
What is MAX amount of separate "piped" values per column value? — Akina
– Akina, Commented Nov 19, 2020 at 8:23

Akina · Accepted Answer · 2020-11-19 10:18:55Z

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, 2020 at 10:18

answered Nov 19, 2020 at 8:26

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

3 Comments

Tim Biegeleisen Over a year ago

@amine Consider accepting this answer in that case.

amine Over a year ago

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. dbfiddle.uk/… :)

Akina Over a year ago

@amine The simplest solution - SELECT DISTINCT .... Updated.

Tim Biegeleisen · Accepted Answer · 2020-11-19 08:41:14Z

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, 2020 at 8:41

answered Nov 19, 2020 at 8:19

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

4 Comments

Tim Biegeleisen Over a year ago

@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.

Akina Over a year ago

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.

Tim Biegeleisen Over a year ago

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.

amine Over a year ago

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.

Collectives™ on Stack Overflow

Substring regex matching in mysql

2 Answers 2

3 Comments

Demo

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related