CODEWITHSUNDEEP
mysqlsql
Lucas Rey
Lucas Rey

Reputation: 467

MySQL search and remove some text in a field

I have to update 2 fields in a table, assuming I have the following data:

Date          CategoryID
2019-04-19    1,92,10
2019-04-18    4,105,10
2019-04-17    3,106,7,78
2019-04-16    3,108,10

I have to update CategoryID and remove the following category numbers if there is/are in row: 106, 107, 108 and 92

So the result will be:

Date          CategoryID
2019-04-19    1,10
2019-04-18    4,105,10
2019-04-17    3,7,78
2019-04-16    3,10

Normally I use the REPLACE function, but in this case I don't know how to use it to remove that category and keep the others. Could someone drive me? Thank you, Lucas

EDIT: REGEXP_REPLACE from @Joakim Danielson did the trick. Thank you to all people who partecipate/reply, however, to all those who have criticized, this is not my code, it's Datalife Engine Blog :)

Upvotes: 1

Views: 340

Answers (4)

Lucas Rey
Lucas Rey

Reputation: 467

REGEXP_REPLACE from @Joakim Danielson did the trick. Thank you to all people who partecipate/reply, however, to all those who have criticized, this is not my code, it's Datalife Engine Blog :)

Upvotes: 0

Joakim Danielson
Joakim Danielson

Reputation: 51945

I use REGEXP_REPLACE with two similar patterns but with the comma , before and after to support the numbers being first, last or somewhere in the. middle

UPDATE test
SET categoryID = REGEXP_REPLACE(categoryID, '((106|107|108|92)([,]{1}))|(([,]{1})(106|107|108|92))', '')

This query is somewhat limited since it will replace both 106 and 1060 for instance. Is this a problem or is the id's limited in range so this is good enough?

Since I assume this is more of a one time thing you could divide it into 3 different updates to make sure you only get exact hits

-- id in the middle
UPDATE test 
SET categoryID = REGEXP_REPLACE(categoryID, ',(106|107|108|92),', ',')

-- id at the start
UPDATE test
SET categoryID = REGEXP_REPLACE(categoryID, '^(106|107|108|92),', '')

-- id at the end
UPDATE test
SET categoryID = REGEXP_REPLACE(categoryID, ',(106|107|108|92)$', '')

Upvotes: 2

edlerd
edlerd

Reputation: 2145

you could use multiple replace calls, i.e. first run queries for for each number with a comma after it. I.e. this replaces 107, with empty string.

Then run queries for each number as single entry. I.e. replace 107 with empty string.

In both cases be aware of partial matches. So if you replace 97 with empty string it will also change id 197 to 1. Or when replacing 97, with empty string you might turn 197,4 into 14.

Upvotes: 1

wallyk
wallyk

Reputation: 57774

That is a horrible schema, at least for use in a relational database.

However, for getting what you asked for, see if this will work:

UPDATE thetable
SET CategoryID = "1,10"
WHERE Date = "2019-04-19"

To make this work, the sql query will have to be generated by a programming language which inspects strings and has some other way to figure out which row to update.

Upvotes: 0

Related Questions