Chapter 10 – Strings
“It’s always been and always will be the same in the world: the horse does the work and the coachman is tipped.”
- Anonymous
The LENGTH Command Counts Characters
The LENGTH command counts the number of characters. If ‘Tom’ was in the Employee_Table, his length would be 3.
The LENGTH Command – Spaces can Count too
If ‘T o m’ was in the Employee_Table, his length would be 5. Yes, spaces in between do count as characters.
The LENGTH Command and Character Data
Last_Name |
Lnth |
Chambers |
20 |
Coffing |
20 |
Harrison |
20 |
Jones |
20 |
Larkins |
20 |
Reilly |
20 |
Smith |
20 |
Smythe |
20 |
Strickling |
20 |
Last_Name has a data type of CHAR (20). Spaces are padded at the end to fill up all 20 characters. This is why the length for each Last_Name is 20.
The LENGTH Needs a TRIM
Last_Name |
Lnth |
Chambers |
8 |
Coffing |
7 |
Harrison |
8 |
Jones |
5 |
Larkins |
7 |
Reilly |
6 |
Smith |
5 |
Smythe |
6 |
Strickling |
10 |
Last_Name has a data type of CHAR (20). Spaces are padded at the end to fill up all 20 characters. To get the true length a TRIM command is needed to remove leading and trailing spaces.
The TRIM Command trims both Leading and Trailing Spaces
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.
A Visual of the TRIM Command Using Concatenation
When you use the TRIM command on a column, that column will have all beginning and ending spaces removed. DB2 uses two pipe symbols for concatenation.
Trim and Trailing is Case Sensitive
For leading and trailing TRIM commands, case sensitivity is important.
For LEADING and TRAILNG TRIM commands, case sensitivity is required.
How to TRIM Trailing Letters
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.
The SUBSTRING Command
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!
How SUBSTRING Works with NO ENDING POSITION
First_Name |
GoToEnd |
Squiggy |
quiggy |
John |
ohn |
Richard |
ichard |
Herbert |
erbert |
Mandee |
andee |
Cletus |
letus |
William |
illiam |
Billy |
illy |
Loraine |
oraine |
If you don’t tell the SUBSTR command the end position, it will go all the way to the end.
An Example using SUBSTRING, TRIM and CHAR Together
SELECT Last_Name
,SUBSTR(Last_Name,
LENGTH( TRIM (Last_Name)) -1, 2) AS Letters
FROM Employee_Table;
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 lengths. We first trimmed the spaces off of Last_Name. Next, we counted the characters in the Last_Name. Then, we subtracted two from the Last_Name character length and passed it to our substring as the starting position.
Concatenation
See those || (double pipe 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 put two Pipe Symbols together. In this example, we have combined the first name, then a single space, and finally the last name to get a new column called ‘Full name’ like Squiggy Jones.
Concatenation and SUBSTRING
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.
Four Concatenations Together
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 with the Last_Name (after we trim it), then a single space, then the First Initial of the First Name, and lastly a Period.
UPPER and LOWER Commands
Upper converts text to uppercase and Lower converts text to lowercase.
LPAD and RPAD
The Lpad () command pads spaces to the left of a string and the Rpad () pads spaces to the right of a string. Notice the spaces in the answer set and notice the lengths.
SOUNDEX
The Soundex command will return a string's SOUNDEX value. SOUNDEX is actually an algorithm that converts any string of text into an alphanumeric pattern describing how it sounds phonetically. Similar sounding characters get the same alphanumeric representation. Above, we were looking for Mandee, but we were not sure how to spell it. We used the Soundex command to phonetically find Mandee. Mondee was close to the sound.