Database Reference
In-Depth Information
SELECT IF
(
CHAR_LENGTH
(
comments
) >
100
),
'long'
,
'short'
)
FROM
bird_sightings
WHERE
sighting_id
=
2
;
Here we're using
CHAR_LENGTH()
to count the number of characters in the
comments
column for the row selected. We're usingthe
IF()
function to determine whether the
character length of the comments is greater than 100 characters. If it is, the function will
return the word
long
. If not, it will return
short
. If this SQL statement was used in an
API script, the value in the
WHERE
clause for the
sighting_id
could be dynamically
replaced for each bird sighting.
CHAR_LENGTH()
understands the character set in current use, as we touched on in
Creating a Database
.
Characters that take up multiple bytes — usually present in Asian
languages — are still considered one character. In contrast,the
LENGTH()
function re-
turns the number of bytes in a given string. Note that there are eight bits to a byte and that
Western languages normally use one byte for each letter. If you want to count the number
of bits, use the
BIT_LENGTH()
function.
As an example, suppose we notice that the
comments
column of the
bird_sightings
table contains some odd binary characters. They have been entered
into the column through the mobile application we provide to members. To narrow the list
of rows that have these odd characters so that we can remove them, we can execute the
following SQL statement:
SELECT
sighting_id
FROM
bird_sightings
WHERE
CHARACTER_LENGTH
(
comments
) !=
LENGTH
(
comments
);
This will give us the
sighting_id
for the rows in which the number of characters does
not equal the number of bytesin the
comments
column.
Comparing and Searching Strings
The previoussubsection used the output of
CHAR_LENGTH()
as input to an
IF()
state-
ment so that we had a choice of what to return. In this subsection, we'll look at some
functions that compare strings, which can also be handy when used with a logical function
such as
IF()
or in a
WHERE
clause.
Let's consider a situation where we might use one of these functions — specifically,the
STRCMP()
function. The name of the function, in the manner much loved by computer
programmers, is a compressed version of “string compare.”
