How to find with partial string from comma separated list of string then need to remove entire string from the list of values

Question

We have requirement in Mysql query, to find with partial string from list of comma separated string. Then need to remove a found a string from comma separated list.

As per the below example, we need to find a string starting with "Pending" then need to remove found string from the comma separated list through Mysql query.

|-------------------|-----------------------------------------|
|         Id        |     Tag                                 |
|-------------------|-----------------------------------------|
|        1          |      Completed, #4CHD, Pending with ABC |
|-------------------|-----------------------------------------|
|        2          |      Open, Pending with Mrg, #4CHD      |
|-------------------|-----------------------------------------|    
|        3          |      Pending with cons, Resolved        |
|-------------------|-----------------------------------------|

Output should be:

|-------------------|-----------------------|
|         Id        |     Tag               |
|-------------------|-----------------------|
|        1          |      Completed, #4CHD |
|-------------------|-----------------------|
|        2          |      Open, #4CHD      |
|-------------------|-----------------------|
|        3          |      Resolved         |
|-------------------|-----------------------|

Akina · Accepted Answer · 2020-10-13 15:53:48Z

1

For MySQL 8+

SELECT test.id, GROUP_CONCAT(TRIM(jsontable.value)) Tag
FROM test
CROSS JOIN JSON_TABLE(CONCAT('["', REPLACE(test.Tag, ',', '","'), '"]'),
                      '$[*]' COLUMNS( value VARCHAR(255) PATH '$')
                      ) jsontable
WHERE TRIM(jsontable.value) NOT LIKE CONCAT(@criteria, '%')
GROUP BY test.id

For MySQL 5.x

SELECT test.id, 
       GROUP_CONCAT(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(test.Tag, ',', nums.num), ',', -1))) Tag
FROM test
JOIN (SELECT 1 num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) nums
    ON nums.num <= 1 + LENGTH(test.Tag) - LENGTH(REPLACE(test.Tag, ',', ''))
WHERE TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(test.Tag, ',', nums.num), ',', -1)) NOT LIKE CONCAT(@criteria, '%')
GROUP BY test.id

fiddle

No rows should be ingnored in output. Let us assume 3rd row has Tag value as "Pending with cons" alone. I this case first two is getting displayed in output and 3rd row is ignored. My requirement is 3rd also should be displayed with empty Tag.

If so LEFT JOIN (and moving condition expression from WHERE to ON) needed:

SELECT test.id, 
       GROUP_CONCAT(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(test.Tag, ',', nums.num), ',', -1))) Tag
FROM test
LEFT JOIN (SELECT 1 num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) nums
    ON nums.num <= 1 + LENGTH(test.Tag) - LENGTH(REPLACE(test.Tag, ',', ''))
    AND TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(test.Tag, ',', nums.num), ',', -1)) NOT LIKE CONCAT(@criteria, '%')
GROUP BY test.id

fiddle

edited Oct 13, 2020 at 15:53

answered Oct 13, 2020 at 9:26

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

6 Comments

Akina Over a year ago

@Mihai For 5.x version one need to parse the CSVs using static/synthetic numbers table and double SUBSTRING_INDEX().

Ramsethu Over a year ago

@Akina Here MySQL installed version is lower than 8.0. I'm facing following error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(CONCAT('["', REPLACE(tik.Tag, ',', '","'), '"]'), '$[*]' ' at line 8

Akina Over a year ago

@Ramsethu MySQL installed version is lower than 8.0 Add into the question precise server version. Also specify maximal separate values amount per Tag value.

Akina Over a year ago

@Ramsethu Updated. Adjust nums as needed.

Ramsethu Over a year ago

@Akina This work fine. Thank you so much for your help. One minor issue i'm facing. No rows should be ingnored in output. Let us assume 3rd row has Tag value as "Pending with cons" alone. I this case first two is getting displayed in output and 3rd row is ignored. My requirement is 3rd also should be displayed with empty Tag. Please look at the url: dbfiddle.uk/…

|

Collectives™ on Stack Overflow

How to find with partial string from comma separated list of string then need to remove entire string from the list of values

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related