Database Reference
In-Depth Information
This specifies a starting point of −25 characters. Because it doesn't specify how many
to extract, MySQL takes the remaining characters from that starting point.
You can use whatever style you prefer.
The
SUBSTRING_INDEX()
is similarto the previous functions, but looks for elements
that separate data within a string. For example, suppose the
prospect_name
column
was constructed differently. Suppose that instead of having fixed width for the title and
names, the text had vertical bars between them. This would be odd for data in a column,
but it is possible. Here's how we could separate the same column containing the vertical
bar character as the separator (the first and third third lines using
SUBSTRING_INDEX()
are fairly understandable, but the second one is more complex):
SELECT
SUBSTRING_INDEX
(
prospect_name
,
'|'
,
1
)
AS
title
,
SUBSTRING_INDEX
(
SUBSTRING_INDEX
(
prospect_name
,
'|'
,
2
),
'|'
, -
1
)
AS
first_name
,
SUBSTRING_INDEX
(
prospect_name
,
'|'
, -
1
)
AS
last_name
FROM
prospects
WHERE
prospect_id
=
7
;
The second argument to
SUBSTRING_INDEX()
tells MySQL how to break the string
into the pieces of text we want. In our example, we use
'|'
to specify the vertical bar.
The number in the third argument tells how many elements to take. So in the first line here
we're saying to get the first element. In the third line, because it has a negative sign in
front of the number, we're saying to count from the end and get one element there. In the
second line, we're using
SUBSTRING_INDEX()
twice, one call embedded inside the
other. The inner call extracts the first two elements. Using those results, we then use an
outer call to extract its first element starting from the end.
Using
SUBSTRING()
is much nicer, but you need to know the starting point and how
many characters to take. In our vertical bar example, we'd need to know exactly where the
vertical bars are in each name. To do that, you will need to use other functions to search
strings. Those are covered in the nextsection.
