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 stackoverflow.com/questions/986826/… — Narendrasingh Sisodia
– Narendrasingh Sisodia, Commented Mar 9, 2016 at 15:51
Do not put lists into a column. If you do, then leave tasks like this to the app. — Rick James
– Rick James, Commented Mar 10, 2016 at 0:24

Dylan Su · Accepted Answer · 2016-03-12 01:27:44Z

1

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)

edited Mar 12, 2016 at 1:27

answered Mar 9, 2016 at 16:39

Dylan Su

6,0751 gold badge18 silver badges26 bronze badges

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

11 Comments

user4826347 Over a year ago

Thanks Dylan, I am trying to run the query and its just not coming well for me.

user4826347 Over a year ago

SELECT FIELDATA, CONCAT_WS( ', ', IF((FIELDATA=SUBSTRING_INDEX( SUBSTRING_INDEX(@str, '@', -5), ';', 1)) LIKE '%@%', NULL, FIELDATA) ) output FROM DATASET WHERE CATID = 2;

user4826347 Over a year ago

the output is the same as column value

Dylan Su Over a year ago

pls provide some of your sample data.

Dylan Su Over a year ago

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.

|

Walter_Ritzel · Accepted Answer · 2016-03-09 19:08:59Z

1

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

edited Mar 9, 2016 at 19:08

answered Mar 9, 2016 at 19:02

Walter_Ritzel

1,3971 gold badge12 silver badges16 bronze badges

6 Comments

user4826347 Over a year ago

Thanks Walter.. Never done functions before. Where do I place the function during run time?

Walter_Ritzel Over a year ago

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

user4826347 Over a year ago

Thats cool. Would it be as easy to create and use in AWS RDS as well?

Walter_Ritzel Over a year ago

Yes, you can use there.

user4826347 Over a year ago

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.

|

Collectives™ on Stack Overflow

Mysql replace using regular expression

2 Answers 2

11 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related