Mysql replace using regular expression

Question

I have a query like the following

SELECT FIELDATA FROM DATASET WHERE CATID = 2;

Output is

21@Marketing;76@Sales

The output can also be the following in case the database does not have multiple values stored.

21@Marketing

I tried the following

SELECT SUBSTRING_INDEX(FIELDATA, '@', -1) AS FIELDATA FROM DATASET WHERE CATID = 2;

but it did not give the desired result.

Is it possible to do string manipulation so that the output becomes Marketing, Sales in the former and just Marketing in the latter.

To summarise The pattern of input

 number1@text1;number2@text2;number3@text;

and the output should be

 text1, text2, text3

Nope you can't that's a feature request http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql#comment33803643_986826 — Narendrasingh Sisodia, Mar 09 '16 at 15:51
Do not put lists into a column. If you do, then leave tasks like this to the app. — Rick James, Mar 10 '16 at 00:24

Dylan Su · Accepted Answer · 2016-03-12T01:27:44.837

Obviously, there is no easy way to match your expectation based on current MySQL functions.

Here is a brute solution to match your expectation, based on separator of "@" and ";".

Sample SQL:

-- data
CREATE TABLE test.DATASET ( ID INT NOT NULL, U_NAME VARCHAR(45) NULL, FIELDATA VARCHAR(255) NULL, PRIMARY KEY (ID)); 
INSERT INTO test.DATASET (ID, U_NAME, FIELDATA) VALUES ('1', 'ALPHA', '21@Marketing;31@Sales'); 
INSERT INTO test.DATASET (ID, U_NAME, FIELDATA) VALUES ('2', 'BETA', '21@Marketing');

-- Query wanted, which can handle up to 5 secsions
SELECT 
    FIELDATA, 
    CONCAT_WS(
        ', ',
        IF((@tmp:=SUBSTRING_INDEX( SUBSTRING_INDEX(FIELDATA, '@', -5), ';', 1)) LIKE '%@%', NULL, @tmp),
        IF((@tmp:=SUBSTRING_INDEX( SUBSTRING_INDEX(FIELDATA, '@', -4), ';', 1)) LIKE '%@%', NULL, @tmp),
        IF((@tmp:=SUBSTRING_INDEX( SUBSTRING_INDEX(FIELDATA, '@', -3), ';', 1)) LIKE '%@%', NULL, @tmp),
        IF((@tmp:=SUBSTRING_INDEX( SUBSTRING_INDEX(FIELDATA, '@', -2), ';', 1)) LIKE '%@%', NULL, @tmp),
        IF((@tmp:=SUBSTRING_INDEX( SUBSTRING_INDEX(FIELDATA, '@', -1), ';', 1)) LIKE '%@%', NULL, @tmp)
    ) output
FROM  test.DATASET JOIN (SELECT @tmp := NULL) v;

-- Query wanted, which can handle up to 2 secsions SELECT FIELDATA, CONCAT_WS( ', ', IF((@tmp:=SUBSTRING_INDEX( SUBSTRING_INDEX(FIELDATA, '@', -2), ';', 1)) LIKE '%@%', NULL, @tmp), IF((@tmp:=SUBSTRING_INDEX( SUBSTRING_INDEX(FIELDATA, '@', -1), ';', 1)) LIKE '%@%', NULL, @tmp) ) output FROM test.DATASET JOIN (SELECT @tmp := NULL) v;

Output:

mysql> -- Query wanted, which can handle up to 5 secsions
mysql> SELECT
    ->     FIELDATA,
    ->     CONCAT_WS(
    ->         ', ',
    ->         IF((@tmp:=SUBSTRING_INDEX( SUBSTRING_INDEX(FIELDATA, '@', -5), ';', 1)) LIKE '%@%', NULL, @tmp),
    ->         IF((@tmp:=SUBSTRING_INDEX( SUBSTRING_INDEX(FIELDATA, '@', -4), ';', 1)) LIKE '%@%', NULL, @tmp),
    ->         IF((@tmp:=SUBSTRING_INDEX( SUBSTRING_INDEX(FIELDATA, '@', -3), ';', 1)) LIKE '%@%', NULL, @tmp),
    ->         IF((@tmp:=SUBSTRING_INDEX( SUBSTRING_INDEX(FIELDATA, '@', -2), ';', 1)) LIKE '%@%', NULL, @tmp),
    ->         IF((@tmp:=SUBSTRING_INDEX( SUBSTRING_INDEX(FIELDATA, '@', -1), ';', 1)) LIKE '%@%', NULL, @tmp)
    ->     ) output
    -> FROM  test.DATASET JOIN (SELECT @tmp := NULL) v;
+-----------------------+------------------+
| FIELDATA              | output           |
+-----------------------+------------------+
| 21@Marketing;31@Sales | Marketing, Sales |
| 21@Marketing          | Marketing        |
+-----------------------+------------------+
2 rows in set (0.00 sec)

mysql> -- Query wanted, which can handle up to 2 secsions
mysql> SELECT
    ->     FIELDATA,
    ->     CONCAT_WS(
    ->         ', ',
    ->         IF((@tmp:=SUBSTRING_INDEX( SUBSTRING_INDEX(FIELDATA, '@', -2), ';', 1)) LIKE '%@%', NULL, @tmp),
    ->         IF((@tmp:=SUBSTRING_INDEX( SUBSTRING_INDEX(FIELDATA, '@', -1), ';', 1)) LIKE '%@%', NULL, @tmp)
    ->         ) output
    -> FROM test.DATASET JOIN (SELECT @tmp := NULL) v;
+-----------------------+------------------+
| FIELDATA              | output           |
+-----------------------+------------------+
| 21@Marketing;31@Sales | Marketing, Sales |
| 21@Marketing          | Marketing        |
+-----------------------+------------------+
2 rows in set (0.00 sec)

Thanks Dylan, I am trying to run the query and its just not coming well for me. — user4826347, Mar 10 '16 at 15:51
SELECT FIELDATA, CONCAT_WS( ', ', IF((FIELDATA=SUBSTRING_INDEX( SUBSTRING_INDEX(@str, '@', -5), ';', 1)) LIKE '%@%', NULL, FIELDATA) ) output FROM DATASET WHERE CATID = 2; — user4826347, Mar 10 '16 at 15:52
My bad. Just replace "@str" with your column name to use in your query. The variable @tmp should be kept. If you want to keey only one IF to match one @, keep the last one , the '-1' one. — Dylan Su, Mar 10 '16 at 15:59
CREATE TABLE `test`.`DATASET` ( `ID` INT NOT NULL, `U_NAME` VARCHAR(45) NULL, `FIELDATA` VARCHAR(255) NULL, PRIMARY KEY (`ID`)); — user4826347, Mar 11 '16 at 18:12
Data: INSERT INTO `test`.`DATASET` (`ID`, `U_NAME`, `FIELDATA`) VALUES ('1', 'ALPHA', '21@Marketing;31@Sales'); INSERT INTO `test`.`DATASET` (`ID`, `U_NAME`, `FIELDATA`) VALUES ('2', 'BETA', '21@Marketing'); — user4826347, Mar 11 '16 at 18:16
I ran the following query: SELECT FIELDATA, CONCAT_WS( ', ', IF((@tmp:=SUBSTRING_INDEX( SUBSTRING_INDEX(FIELDATA, '@', -5), ';', 1)) LIKE '%@%', NULL, @tmp), IF((@tmp:=SUBSTRING_INDEX( SUBSTRING_INDEX(FIELDATA, '@', -4), ';', 1)) LIKE '%@%', NULL, @tmp) ) output FROM test.DATASET ; — user4826347, Mar 11 '16 at 18:16
Please check the updated post. I verified with data you post in comments. — Dylan Su, Mar 12 '16 at 01:28
Thanks Dylan, It is giving me results. Although I have one questions why do we need to use the JOIN ? "JOIN (SELECT @tmp := NULL) v" as without join I am getting the desired result. — user4826347, Mar 12 '16 at 17:58

Walter_Ritzel · Answer 2 · 2016-03-09T19:08:59.653

Here is my tentative on your problem. I've found some functions on the web (I'll put the credits later) and have combined them to make it work for your specific need: you need to get a clean string with 2 levels of delimiters (@ and ;).

See below:

DELIMITER $$

CREATE FUNCTION splitter(x VARCHAR(255), delim VARCHAR(12), pos INT)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');
END$$

CREATE FUNCTION substrCount(s VARCHAR(255), ss VARCHAR(255)) 
RETURNS tinyint(3) unsigned
READS SQL DATA
BEGIN
  DECLARE count TINYINT(3) UNSIGNED;
  DECLARE offset TINYINT(3) UNSIGNED;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s = NULL;
  SET count = 0;
  SET offset = 1;
  REPEAT
    IF NOT ISNULL(s) AND offset > 0 THEN
      SET offset = LOCATE(ss, s, offset);
      IF offset > 0 THEN
        SET count = count + 1;
        SET offset = offset + 1;
      END IF;
    END IF;
   UNTIL ISNULL(s) OR offset = 0 END REPEAT;
   RETURN count;
END$$

CREATE FUNCTION split_str(x varchar(255), delim varchar(12), delim2 varchar(12), return_separator varchar(12)) RETURNS varchar(255)
BEGIN
  SET @Valcount = substrCount(x,delim)+1;
  SET @v1=0;
  SET @v2='';
  WHILE (@v1 < @Valcount) DO
    set @val = splitter(x,delim,@v1+1);
    if @v1 = 0 then
      SET @v2 = world.split_str(@val,delim2,2);
    else
      SET @v2 = concat(@v2,return_separator,world.split_str(@val,delim2,2));
    end if;
    SET @v1 = @v1 + 1;
  END WHILE;
  return @v2;
END$$
DELIMITER ;

Then, you can use it like below, assuming that you have a table t2 with a field f2 that contains your sample string "number1@text1;number2@text2;number3@text;":

select split_str(f2,';','@',', '), f2 from t2

The result will be:

text1, text2, text

As promissed, the authors:

Function substrCount created by Andrew Hanna on August 24 2006 8:04pm

Function splitter created by Federico Cargnelutti on 2009

Function split_str created by mtlseo on 2012

Thanks Walter.. Never done functions before. Where do I place the function during run time? — user4826347, Mar 11 '16 at 18:06
You can simply run the creation script once in your database and then use like in the example: select split_str(f2,';','@',', '), f2 from t2 — Walter_Ritzel, Mar 11 '16 at 18:32
Thats cool. Would it be as easy to create and use in AWS RDS as well? — user4826347, Mar 12 '16 at 18:09
This solution also works well, but for easier roll out I used Dylan Su's answer. Since it has been tested and used, I chose Dylan Su's one as accepted answer. But I would have to really appreciate Walter_Ritzel answer which is generic can be used across the places. I will be eventually using this solution. — user4826347, Mar 19 '16 at 20:52
It is ok. But mine was tested and used by many people as well (just not by me), as this is the solution recommended in any MySQL forum until MySQL implements the regex_replace native. — Walter_Ritzel, Mar 19 '16 at 21:18

Mysql replace using regular expression

2 Answers2