Database Reference
In-Depth Information
FROM prospects LIMIT 4;
+-------+---------------------------+---------------------------+
| title | first_name | last_name |
+-------+---------------------------+---------------------------+
| Ms | Caryn-Amy | Rose |
| Mr | Kenneth | Dyer |
| Mr | Colin | Charles |
| Ms | Sveta | Smirnova |
+-------+---------------------------+---------------------------+
In the example's
LEFT()
function, the starting point for extracting data is the first char-
acter. The number we gave as an argument (i.e., 2), is the number of characters we want to
extract starting from the first. The
RIGHT()
function is similar, but it starts from the last
character on the right, counting left. The
MID()
function is a little different. With it, you
can specify the starting point (i.e., the fifth character in our example) and how many char-
acters you want (i.e., 25 characters).
The
SUBSTRING()
functionis synonymous with
MID()
and their syntax is the same.
By default, if the number of characters to capture isn't specified, it's assumed that all the
remaining ones are to be extracted. This makes these functions work like the
LEFT()
function. If the second argument to
SUBSTRING()
or
MID()
is a negative number, the
function will start from the end of the string, making it likethe
RIGHT()
function.
Because the
SUBSTRING()
function is so versatile, we can use it to accomplish all the
text extraction in the previous example. Theequivalent
SELECT
would look like this:
SELECT
SUBSTRING
(
prospect_name
,
1
,
2
)
AS
title
,
SUBSTRING
(
prospect_name
FROM
5
FOR
25
)
AS
first_name
,
SUBSTRING
(
prospect_name
, -
25
)
AS
last_name
FROM
prospects
LIMIT
3
;
This example shows three ways to use
SUBSTRING()
:
SUBSTRING(prospect_name, 1, 2) AS title
This has the same syntax we have used for other functions in this section: three argu-
ments to specify the column with the text, the starting point for extracting text, and the
number of characters to extract.
SUBSTRING(prospect_name FROM 5 FOR 25) AS first_name
This shows a different, wordier syntax. The starting point here is 5 and the number of
characters to extract is 25.
SUBSTRING(prospect_name, -25) AS last_name
