Pattern Matching with Strings - Breaking Apart or Combining Strings

To combine strings rather than pull them apart, use the CONCAT() function. It concatenates all its arguments and returns the result:

  mysql> SELECT CONCAT('Hello, ',USER(),', welcome to MySQL!') AS greeting;

 

greeting

Hello, cbuser@localhost, welcome to MySQL!

  mysql> SELECT CONCAT(name,' ends in "d": ',IF(RIGHT(name,1)='d','YES','NO'))
     -> AS 'ends in "d"?'
     -> FROM metal;
  +--------------------------+
  | ends in "d"?             |
  +--------------------------+
  | copper ends in "d": NO   |
  | gold ends in "d": YES    |
  | iron ends in "d": NO     |
  | lead ends in "d": YES    |
  | mercury ends in "d": NO  |
  | platinum ends in "d": NO |
  | silver ends in "d": NO   |
  | tin ends in "d": NO      |
  +--------------------------+

Concatenation can be useful for modifying column values “in place.” For example, the following UPDATE statement adds a string to the end of each name value in the metal table:

  mysql> UPDATE metal SET name = CONCAT(name,'ide');
  mysql> SELECT name FROM metal;
  +-------------+
  | name        |
  +-------------+
  | copperide   |
  | goldide     |
  | ironide     |
  | leadide     |
  | mercuryide  |
  | platinumide |
  | silveride   |
  | tinide      |
  +-------------+

To undo the operation, strip off the last three characters (the CHAR_LENGTH() function returns the length of a string in characters):

  mysql> UPDATE metal SET name = LEFT(name,CHAR_LENGTH(name)-3);
  mysql> SELECT name FROM metal;
  +----------+
  | name     |
  +----------+
  | copper   |
  | gold     |
  | iron     |
  | lead     |
  | mercury  |
  | platinum |
  | silver   |
  | tin      |
  +----------+

The concept of modifying a column in place can be applied to ENUM or SET values as well, which usually can be treated as string values even though they are stored internally as numbers. For example, to concatenate a SET element to an existing SET column, use CONCAT() to add the new value to the existing value, preceded by a comma. But remember to account for the possibility that the existing value might be NULL. In that case, set the column value equal to the new element, without the leading comma:

  UPDATE tbl_name
  SET set_col = IF(set_col IS NULL,val,CONCAT(set_col,',',val));

Please check back next week for the continuation of this article.