Archives

The List

« BINARY :: MySQL Function of the Day | Main | MySQL Pop Quiz #21 »

SUBSTRING() :: MySQL Function of the Day

By Carsten | March 28, 2008

Function name: SUBSTRING()
Aliases: MID(), SUBSTR()
Function type: String function
Purpose: Extract part of a string from another string

Description: SUBSTRING(string <part definiton>) returns a portion of string. Which part exactly may be defined in several different ways. With just a single number x, the substring starting at character x and the remainder of string will be returned. x may be negative, in which case the starting position is counted from the end, not the start of the string.

With two numbers, the first number determines the starting character and the second the number of characters to return.

Both numbers must be (something which will convert into) integers. The first character in a string has position 1, not 0 as you may expect from some programming languages.

Note that we’re talking characters here, not bytes. So your UTF-8 encoded strings are safe to use with this function.

In total, there are four ways of specifying the <part definiton>:

-- return part of string starting at start_pos to end of string:

SUBSTRING('string', start_pos)

-- same as above, using standard SQL syntax:

SUBSTRING('string' FROM start_pos)

-- return part of string starting at start_pos and n-1 number of characters following that:

SUBSTRING('string', start_pos, n)

 -- as above, using standard SQL syntax:

SUBSTRING('string' FROM start_pos FOR n)

Below, we see 8 different ways of extracting the last 6 characters (‘bit.dk’) from ‘bitbybit.dk’:

SUBSTRING('bitbybit.dk', 6),

SUBSTRING('bitbybit.dk' FROM 6),

SUBSTRING('bitbybit.dk', 6, 6),

SUBSTRING('bitbybit.dk' FROM 6 FOR 6),

SUBSTRING('bitbybit.dk', -6),

SUBSTRING('bitbybit.dk' FROM -6),

SUBSTRING('bitbybit.dk', -6, 6),

SUBSTRING('bitbybit.dk' FROM -6 FOR 6)

If you’re more used to C or PHP, you can use SUBSTR() if that makes you more happy. It works exactly as SUBSTRING(). And if you come from an ODBC background, you may be happier using the MID() function, which is another alias of SUBSTRING() . It even understands the bits regarding FROM and FOR, even if the manual won’t admit to it. :-)

MySQL manual entry on SUBSTRING

[fadps]

Topics: MySQL Function of the day |

Comments