CODEWITHSUNDEEP
mysql
DeweyOx
DeweyOx

Reputation: 719

Updating a Delimited String Value in MySQL

Let's say I have a column value of '1|2|3|4|5'. These values are a string representation of an array, and I'd like to remove a single value in the string (by integer) resulting in '1|2|4|5'. What is the most efficient way in MySQL to do that given that the integer to be removed could be anywhere in the string?

Upvotes: 1

Views: 91

Answers (2)

fthiella
fthiella

Reputation: 49049

You could use something like this:

UPDATE yourtable
SET col = TRIM(BOTH '|' FROM REPLACE(
            REPLACE(
              CONCAT('|',REPLACE(col, '|', '||'), '|'),
              '|3|', '')
            , '||', '|')
          )

Here I double every | to ||, I then add a | at the beginning and one at the end. This way, every element has its own | at the start and | at the end.

Now we can safely replace |3| with an empty string.

We now need to convert double || back to |, and trim the string to eventually remove the | at the beginning and the one at the end.

See it working here.

If you are sure that element 3 cannot be present more than once, you can skip doubling | to ||, and you have to replace |3| with |. The rest is the same.

Upvotes: 1

Deepak Singhal
Deepak Singhal

Reputation: 10866

update table set col=replace ( col, '3','')

This will do a case sensitive search on the column. If its a huge table, have an index on this column. Results will be reasonably fast.

Upvotes: 0

Related Questions