Search code examples
mysqlsqlstringstring-concatenation

How to replace any string with a concatenation of another string and one field in MySQL

I'd like to replace ANY string (empty or whatever) in field1 with a concatenation of myString + field2. How can I do it?

This is what I tryed and it doesn't work:

UPDATE table SET field1 = REPLACE(field1, '%', CONCAT(myString, field2));

I guess the problem is the '%' since I don't know how to match ANY string.

Solution

  • You would do this as:

    UPDATE t  -- this is the name of the TABLE, not the COLUMN
    SET field1 = CONCAT(COALESCE(mystring, ''), COALESCE(field1, ''));

    The % is a wildcard character used only for LIKE. It is not some sort of general wildcard. If you want to concatenate two other values:

    UPDATE t  -- this is the name of the TABLE, not the COLUMN
    SET field1 = CONCAT(COALESCE(mystring, ''), COALESCE(field2, ''));

    Of course, COALESCE() is only necessary if you want to treat NULL as an empty string (otherwise CONCAT() returns NULL).