Search code examples
mysqlsubstringtrimregexp-replaceinstr

Substring regex matching in mysql

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

Solution 
  • 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