Mysql - Way to update portion of a string?

sql

string-manipulation

regex

conditional-logic

byNikita Barsukov·Oct 7, 2024

Here's a quick fix: you can easily update part of a string in MySQL using REPLACE function:

UPDATE your_table
SET your_column = REPLACE(your_column, 'old_string', 'new_string')
WHERE your_column LIKE '%old_string%';

This one-liner geeks' magic wand swaps 'old_string' with 'new_string' in any row meeting the condition.

Adjusting size? Use CHAR_LENGTH

Have you ever faced a situation where the new string size doesn't match with the old one? MySQL has a solution called CHAR_LENGTH:

UPDATE your_table
SET your_column = CONCAT(
  LEFT(your_column, CHAR_LENGTH('old_string')),
  'new_string',
  SUBSTRING(your_column, CHAR_LENGTH('old_string') + 1)
)
WHERE your_column LIKE '%old_string%';

Here, CHAR_LENGTH behaves like a tailor's tape, measures 'old_string' and paves way for 'new_string'. Fashionable, isn't it?

Meet the Regex family

When you want to perform some string acrobatics, bring out the big guns. Meet REGEXP, your helper for complex pattern matching:

UPDATE your_table
SET your_column = REGEXP_REPLACE(your_column, 'old_pattern', 'new_string')
WHERE your_column REGEXP 'old_pattern';

Just like a word gymnast, REGEXP can bend and twist to match all sorts of patterns.

It's time for some logic — introducing CASE

Sometimes, updates depend on various conditions. In MySQL, CASE is your friend handling conditional logic:

UPDATE your_table
SET your_column = CASE
  WHEN your_column LIKE '%condition1%' THEN REPLACE(your_column, 'old_string1', 'new_string1')
  WHEN your_column LIKE '%condition2%' THEN REPLACE(your_column, 'old_string2', 'new_string2')
  ELSE your_column
END;

Think of CASE as the ultimate "decision-making robot" in your SQL toolkit.

Trim to stay fit

For a final touch, the TRIM function lets you shed some unwanted characters from your strings. Stay fit!

UPDATE your_table
SET your_column = TRIM(BOTH 'Character_to_remove' FROM your_column);

TRIM is like a barber for your string, snipping away unnecessary characters. A clean shave, please!

Concatenation for perfect match

For precision updates, use CONCAT to sew parts of the original string with the new one:

UPDATE your_table
SET your_column = CONCAT(
  SUBSTRING(your_column, 1, CHAR_LENGTH('old_string') - 1),
  'new_string',
  SUBSTRING(your_column FROM CHAR_LENGTH('old_string') + 1)
)
WHERE your_column LIKE '%old_string%';

CONCAT is like a perfect tailor -- it takes your measurements (CHAR_LENGTH) and stitches so precisely you won't even find a seam!

Are you a decision maker? Use CASE

The CASE statement can set up intricate criteria for updates:

UPDATE your_table
SET your_column = CASE
  WHEN your_column LIKE '%bob%' THEN REPLACE(your_column, 'bob', 'Robert')
  WHEN your_column LIKE '%cafe%' THEN REPLACE(your_column, 'cafe', 'Cafeteria')
  ELSE your_column
END
WHERE your_column LIKE '%bob%' OR your_column LIKE '%cafe%';