Pattern Matching with Strings - 5.13 Breaking Apart or Combining Strings

Problem

You want to extract a piece of a string or combine strings to form a larger string.

Solution

To obtain a piece of a string, use a substring-extraction function. To combine strings, use CONCAT().

Discussion

Strings can be broken apart by using appropriate substring-extraction functions. For example, LEFT(), MID(), and RIGHT() extract substrings from the left, middle, or right part of a string:

  mysql> SELECT name, LEFT(name,2), MID(name,3,1), RIGHT(name,3) FROM metal;

name	LEFT(name,2) | MID(name,3,1)  RIGHT(name,3)
copper		co		p		per
gold	go				l		old
iron	ir				o		ron
lead	le				a		ead
mercury			me		r		ury
platinum | pl					a		num
silver		si		l		ver
tin		ti		n		tin

For LEFT() and RIGHT(), the second argument indicates how many characters to return from the left or right end of the string. For MID(), the second argument is the starting position of the substring you want (beginning from 1), and the third argument indicates how many characters to return.

The SUBSTRING() function takes a string and a starting position, returning everything to the right of the position. MID() acts the same way if you omit its third argument because MID() is actually a synonym for SUBSTRING():

  mysql> SELECT name, SUBSTRING(name,4), MID(name,4) FROM metal;

 

name	SUBSTRING(name,4) MID(name,4)
copper			| per	| per
gold			d	d
iron			n	n
lead			d	d
mercury			cury	cury
platinum tinum				tinum
silver		ver		ver

 

tin

Use SUBSTRING_INDEX(str,c,n) to return everything to the right or left of a given character. It searches into a string str  for the n-th occurrence of the character c and returns everything to its left. If n  is negative, the search for c  starts from the right and returns everything to the right of the character:

  mysql> SELECT name,
     -> SUBSTRING_INDEX(name,'r',1),
     -> SUBSTRING_INDEX(name,'i',-1)
     -> FROM metal;

 

name	SUBSTRING_INDEX(name,'r',1) SUBSTRING_INDEX(name,'i',-1)
copper		coppe		copper
gold	gold				gold
iron	i				ron
lead	lead				lead
mercury			me		mercury
platinum  platinum					num
silver		silve		lver
tin		tin		n

Note that if there is no n-th occurrence of the character, SUBSTRING_INDEX() returns the entire string. SUBSTRING_INDEX() is case-sensitive.

Substrings can be used for purposes other than display, such as to perform comparisons. The following statement finds metal names having a first letter that lies in the last half of the alphabet:

  mysql> SELECT name from metal WHERE LEFT(name,1) >= 'n';
  +----------+
  | name     |
  +----------+
  | platinum |
  | silver   |
  | tin      |
  +----------+

Next: Breaking Apart or Combining Strings >>
 

More Database Articles Articles
More By O'Reilly Media