"The man on top of the mountain did not fall there."
- Longfellow
The LENTH command counts the number of characters. If 'Tom' was in the Employee_Table, his length would be three.
If ‘T o m’ was in the Employee_Table, his length would be 5. Yes, spaces in between do count as characters.
Last_Name |
Lnth |
Chambers |
20 |
Coffing |
20 |
Harrison |
20 |
Jones |
20 |
Larkins |
20 |
Reilly |
20 |
Smith |
20 |
Smythe |
20 |
Strickling |
20 |
The LENGTH command counts characters, but it also counts leading and trailing spaces. A TRIM command can be used.
Last_Name |
Lnth |
Chambers |
8 |
Coffing |
7 |
Harrison |
8 |
Jones |
5 |
Larkins |
7 |
Reilly |
6 |
Smith |
5 |
Smythe |
6 |
Strickling |
10 |
The TRIM command trims trailing and leading spaces. Above, we first trimmed the Last_Name column and then we were able to get a accurate length of the Last_Name.
UPPER will convert text to uppercase and LOWER will convert text to lowercase.
The LOWER function converts all letters in a specified string to lowercase letters. If there are characters in the string that are not letters, they are not affected by the LOWER command.
The UPPER function converts all letters in a specified string to uppercase letters. If there are characters in the string that are not letters, they are not affected by the UPPER command.
The UPPER and LOWER functions convert all letters in a specified string to either upper or lower case letters. If there are characters in the string that are not letters, they are not affected by the UPPER or LOWER commands. Notice in our example that the numbers 1 and 2 were unaffected by the LOWER and UPPER commands.
The CHARACTERS command counts the number of characters. If ‘Tom’ was in the Employee_Table, his length would be three.
Last_Name |
Lnth |
Chambers |
20 |
Coffing |
20 |
Harrison |
20 |
Jones |
20 |
Larkins |
20 |
Reilly |
20 |
Smith |
20 |
Smythe |
20 |
Strickling |
20 |
The CHARACTERS command brings back a length even for Char (20) data type.
Last_Name |
Lnth |
Chambers |
8 |
Coffing |
7 |
Harrison |
8 |
Jones |
5 |
Larkins |
7 |
Reilly |
6 |
Smith |
5 |
Smythe |
6 |
Strickling |
10 |
The TRIM command trims trailing and leading spaces. Above, we first trimmed the Last_Name column and then we were able to get a accurate count of the characters in the Last_Name.
Query 1
SELECT First_Name
,CHARACTER_Length(First_Name) AS C_Length
FROM Employee_Table ;
Query 2
SELECT First_Name
,Octet_Length (First_Name) AS C_Length
FROM Employee_Table ;
Query 3
SELECT First_Name
,Length (First_Name) AS C_Length
FROM Employee_Table ;
You can also use the OCTET LENGTH command. These three queries get the same exact answer sets!
Query 1
SELECT Last_Name
,Trim(Last_Name) AS No_Spaces
FROMEmployee_Table ;
Query 2
SELECT Last_Name
,Trim(Both from Last_Name) AS No_Spaces
FROMEmployee_Table ;
Both queries above do the exact same thing.
They remove spaces from the beginning and
the end of the column Last_Name.
Both queries trim both the leading and trailing spaces from Last_Name.
SELECT ' Rodriquez '
,LENGTH (Trim (Trailing FROM ' Rodriquez ')) AS Front_Spaces ;
' Rodriquez ' |
Front_Spaces |
Rodriquez |
11 |
The TRAILING FROM Command allows you to only TRIM the spaces behind the Last_Name. Now, we will still get a character count of 11 because we are only cutting off the trailing spaces and not the beginning spaces.
Concatenation allows you to combine multiple columns into one column. The || (Pipe Symbol) on your keyboard is just above the ENTER key. Don’t put a space in between, but just put two Pipe Symbols together. In this example, we have combined the first name, then a single space, and then the last name to get a new column called Full_Name.
When you use the TRIM command on a column, that column will have all beginning and ending spaces removed.
For LEADING and TRAILNG TRIM commands, case sensitivity is required.
The above example removed the trailing ‘y’ from the First_Name and the trailing ‘g’ from the Last_Name. Remember that this is case sensitive.
First_Name |
Quiz |
Squiggy |
qui |
John |
ohn |
Richard |
ich |
Herbert |
erb |
Mandee |
and |
Cletus |
let |
William |
ill |
Billy |
ill |
Loraine |
ora |
This is a SUBSTRING. The substring is passed two parameters, and they are the starting position of the string and the number of positions to return (from the starting position). The above example will start in position 2 and go for 3 positions!
Query 1 with Substring
SELECT First_Name,
SUBSTRING(First_Name FROM 2 for 3) AS Quiz
FROMEmployee_Table ;
Query 2 with Substr
SELECT First_Name,
SUBSTR (First_Name , 2 ,3) AS Quiz2
FROM Employee_Table ;
Both queries above are going to yield the same results! SUBSTR is just a different way of doing a substring. Both have two parameters in starting position and number of characters to return.
If you don’t tell the Substring the end position, it will go all the way to the end.
A starting position of zero moves one space in front of the beginning. Notice that our FOR Length is 6 so ‘Squiggy’ turns into ‘ Squig’. The point being made here is that both the starting position and ending positions can move backwards which will come in handy as you see other examples.
A starting position of -1 moves two spaces in front of the beginning. Notice that our FOR Length is 3, so each name delivers only the first initial. The point being made here is that both the starting position and ending positions can move backwards which will come in handy as you see other examples.
In our example above, we start in position 3, but we go for zero positions, so nothing is delivered in the column that is aliased WhatsUp.
Last_Name |
Letters |
Jones |
es |
Smith |
th |
Smythe |
he |
Harrison |
on |
Chambers |
rs |
Strickling |
ng |
Reilly |
ly |
Coffing |
ng |
Larkins |
ns |
The SQL above brings back the last two letters of each Last_Name even though the last names are of different length. We first trimmed the spaces off of Last_Name. Then we counted the characters in the Last_Name. Then we subtracted two from the Last_Name character length and then passed it to our substring as the starting position. We then go for two positions.
SELECT Last_Name
,Position ('e' in Last_Name) AS Find_The_E
,Position ('f' in Last_Name) AS Find_The_F
FROMEmployee_Table ;
This is the position counter. What it will do is tell you what position a letter is on. Why did Jones have a 4 in the result set? The ‘e’ was in the 4th position. Why did Smith get a zero for both columns? There is no ‘e’ in Smith and no ‘f’ in Smith. If there are two ‘f’s, only the first occurrence is reported.
See those || symbols? Those represent concatenation. That allows you to combine multiple columns into one column. The || (Pipe Symbol) on your keyboard is just above the ENTER key. Don’t put a space in between, but just put two Pipe Symbols together. In this example, we have combined the first name, then a single space and then the last name to get a new column called ‘Full name’ like Squiggy Jones.
Of the three items being concatenated together, what is the first item of concatenation in the example above? The first initial of the First_Name. Then, we concatenated a literal space and a period. Then, we concatenated the Last_Name.
Why did we TRIM the Last_Name? To get rid of the spaces or the output would have looked odd. How many items are being concatenated in the example above? There are 4 items concatenated. We start by trimming the Last_Name. Then we concatenate a single space. Then, we concatenate the first initial of the first name. And finally we concatenate a period.
What happened above to cause the error? Can you see it? The Pipe Symbols || have a space between them like | |, when it should be ||. It is a tough one to spot, so be careful.