This chapter is from the book
This chapter is from the book
This chapter is from the book
Character Functions
Character functions are those that enable you to manipulate character data. Just as character datatypes are sometimes called string datatypes, character functions are sometimes called string functions. We’ll cover these seven examples of character functions: LEFT, RIGHT, SUBSTRING, LTRIM, RTRIM, UPPER, and LOWER.
In this chapter, rather than retrieving data from specific tables, we’ll simply use SELECT statements with literal values in the columnlist. There will be no FROM clause to indicate a table. Let’s start with an example for the LEFT function. When this SQL command is issued:
SELECT
LEFT('sunlight',3) AS 'The Answer'
this data is returned:
The inclusion of a column alias in this SQL statement allows the output to display “The Answer” as a column header. Note that there is no FROM clause in the SELECT statement. Instead of retrieving data from a table, we’re selecting data from a single literal value, namely ‘sunlight’. In many SQL implementations, including SQL Server and MySQL, a FROM clause isn’t strictly necessary in a SELECT statement, although in practice one would seldom write a SELECT statement like this. We’re using this format, without a FROM clause, only to more easily illustrate how functions work.
Let’s now look at the format of this function in greater detail. The general format of the LEFT function is:
LEFT(CharacterValue, NumberOfCharacters)
All functions have any number of arguments within the parentheses. For example, the LEFT function has two arguments: CharacterValue and NumberOfCharacters. The term argument is a commonly used mathematical term that describes a component of functions, and has nothing to do with anything being disagreeable or unpleasant. The various arguments that are defined for each function are what truly define the meaning of the function. In the case of the LEFT function, the CharacterValue and NumberOfCharacters arguments are both needed to define what will happen when the LEFT function is invoked.
The LEFT function has two arguments, and both are required. As mentioned, other functions may have more or fewer arguments. Functions are even permitted to have no arguments. But regardless of the number of arguments, even if zero, all functions have a set of parentheses following the function name. The presence of the parentheses tells you that the expression is a function and not something else.
The formula for the LEFT function says: Take the specified CharacterValue, look at the specified NumberOfCharacters on the left, and bring back the result. In the previous example, it looks at the CharacterValue ‘sunlight’ and brings back the left three characters. The result is “sun”.
The main point to remember is that for any function you want to use, you’ll need to look up the function in the database’s reference guide and determine how many arguments are required and what they mean.
The second character function we’ll cover is the RIGHT function. This is the same as the LEFT function, except that characters are now specified for the right side of the input value. The general format of the RIGHT function is:
RIGHT(CharacterValue NumberOfCharacters)
As an example:
SELECT
RIGHT('sunlight',5) AS 'The Answer'
returns:
In this case, the NumberOfCharacters argument needed to have a value of 5 in order to return the value “light”. A value of 3 would have only returned “ght”.
One problem that often arises with the use of the RIGHT function is that character data often contains spaces on the right-hand side. Let’s look at an example in which a table with only one row of data contains a column named President, where the column is defined as being 20 characters long. The table looks like:
If we issue this SELECT statement against the table:
SELECT RIGHT(President,10) AS 'Last Name' FROM table1
we get back this data:
We expected to get back “Washington” but only got “hington.” The problem is that the entire column is 20 characters long. In this example, there are three spaces to the right of the value “George Washington”. Therefore, when we ask for the rightmost 10 characters, SQL will take the three spaces, plus another seven characters from the original expression. As will soon be seen, the function RTRIM must be used to remove the ending spaces before using the RIGHT function.
You might be wondering how to select data from the middle of an expression. This is accomplished by using the SUBSTRING function. The general format of that function is:
SUBSTRING(CharacterValue StartingPosition, NumberOfCharacters)
For example:
SELECT
SUBSTRING('thewhitegoat',4,5) AS 'The Answer'
returns this data:
This function is saying to take five characters, starting with position 4. This results in the display of the word “white”.
Our next two character functions enable us to remove all spaces, either on the left or the right side of an expression. The LTRIM function trims characters from the left side of a character expression. For example:
SELECT
LTRIM(' the apple') AS 'The Answer'
returns this data:
Note that LTRIM is smart enough not to eliminate spaces in the middle of a phrase. It only removes the spaces to the very left of a character value.
Similar to LTRIM, the RTRIM function removes any spaces to the right of a character value. An example of RTRIM will be given in the next section, on composite functions.
The final two character functions to be covered are UPPER and LOWER. These functions convert any word or phrase to upper- or lowercase. The syntax is simple and straightforward. Here’s an example that covers both functions:
SELECT
UPPER('Abraham Lincoln') AS 'Convert to Uppercase',
LOWER('ABRAHAM LINCOLN') AS 'Convert to Lowercase'
The output is:
)