Pattern Matching with Strings - Breaking Apart or Combining Strings

(Page 5 of 5 )

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.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

Comments On: Pattern Matching with Strings

>>> Be the FIRST to comment on this article!

Buy this book...

This article is excerpted from chapter five of the MySQL Cookbook, Second Edition, written by Paul DuBois (O'Reilly; ISBN: 059652708X). Check it out today at your favorite bookstore. Buy this book now.