Database Reference
In-Depth Information
Extracting Text
There are a fewfunctions for extracting a piece of text from a string. You indicate the point
from which to start selecting text and how much text you want. There are four such func-
tions:
LEFT()
,
MID()
,
RIGHT()
, and
SUBSTRING()
. The
SUBSTRING_INDEX()
function is also related. We'll look at each one here.
Let's look atthe
LEFT()
,
MID()
, and
RIGHT()
functions first. Suppose our marketing
agency acquires a table called
prospects
containing a list of people who are known to
be bird-watchers. Each person's title and first and last name is stored in a column called
prospect_name
, with email addresses in another column. The
prospect_name
column is a fixed character length data type,
CHAR(54)
. The marketing agency tells us
that the title is contained in the first four characters, the first name in the next 25, and the
last name in the remaining 25. For the titles, they're using only
Mr.
and
Ms.
with a space
after each — hence the first four characters — but we will extract just the first two charac-
ters for our tables. Let's see how that column looks by executing a simple
SELECT
to re-
trieve four names:
SELECT prospect_name
FROM prospects LIMIT 4;
+--------------------------------------------------------+
| prospect_name |
+--------------------------------------------------------+
| Ms. Caryn-Amy Rose |
| Mr. Colin Charles |
| Mr. Kenneth Dyer |
| Ms. Sveta Smirnova |
+--------------------------------------------------------+
As you can see, the data is a fixed width for each element. Normally, with a
CHAR
column,
MySQL would not store the trailing spaces. Whoever created this table enforced the rigid
format (4, 25, and 25 characters) byexecuting
SET sql_mode =
'PAD_CHAR_TO_FULL_LENGTH';
before inserting data into the column.
With an
INSERT INTO…SELECT
statement and a few functions, we can extract and sep-
arate the data we need and put these prospects in a new table we created that we call
mem-
bership_prospects
. Let's execute the
SELECT
first totest our organization of the
functions before we insert the data:
SELECT LEFT(prospect_name, 2) AS title,
MID(prospect_name, 5, 25) AS first_name,
RIGHT(prospect_name, 25) AS last_name
