Database Reference
In-Depth Information
▼
Analysis
As you can see,
Upper()
converts text to uppercase and so in this example
each vendor is listed twice, first exactly as stored in the
vendors
table, and
then converted to uppercase as column
vend_name_upcase
.
Table 11.1 lists some commonly used text-manipulation functions.
Table 11.1
Commonly Used Text-Manipulation Functions
Function
Description
Left()
Returns characters from left of string
Length()
Returns the length of a string
Locate()
Finds
a substring within a string
Lower()
Converts string to lowercase
LTrim()
Trims
white space from left of string
Right()
Returns characters from right of string
RTrim()
Trims white space from right of string
Soundex()
Returns a string's SOUNDEX value
SubString()
Returns characters from within a string
Upper()
Converts string to uppercase
One item in Table 11.1 requires further explanation. SOUNDEX is an algo-
rithm that converts any string of text into an alphanumeric pattern describing
the phonetic representation of that text. SOUNDEX takes into account similar
sounding characters and syllables, enabling strings to be compared by how they
sound rather than how they have been typed. Although SOUNDEX is not a
SQL concept, MariaDB (like many other DBMSs) offers SOUNDEX support.
Here's an example using the
Soundex()
function. Customer
Coyote Inc.
is in the
customers
table and has a contact named
Y. Lee
. But what if that
were a typo, and the contact actually was supposed to have been
Y. Lie
?
Obviously, searching by the correct contact name would return no data, as
shown here:
▼
Input
SELECT cust_name, cust_contact
FROM customers
WHERE cust_contact = 'Y. Lie';
▼
Output
+-------------+--------------+
| cust_name | cust_contact |
+-------------+--------------+
