Chapter 9 – 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 Char (20) 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. Oracle 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.
How SUBSTRING Works with a Starting Position of -1
First_Name |
Before2 |
Squiggy |
S |
John |
J |
Richard |
R |
Herbert |
H |
Mandee |
M |
Cletus |
C |
William |
W |
Billy |
B |
Loraine |
L |
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.
How SUBSTRING Works with an Ending Position of 0
First_Name |
WhatsUp |
Squiggy |
? |
John |
? |
Richard |
? |
Herbert |
? |
Mandee |
? |
Cletus |
? |
William |
? |
Billy |
? |
Loraine |
? |
In our example above, we start in position 3, but we go for zero positions, so nothing is delivered in the column. That is what’s up!
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. The tricky part is that the last names are different lengths. We first trimmed the spaces off of the Last_Name. Next, we counted the characters in the Last_Name. Lastly, we subtracted two from the Last_Name character length and passed it to our substring as the starting position.
Concatenation
Two pipe symbols 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, 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.
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, otherwise 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 we have a single space, then we have the First Initial of the First Name, and then we have 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 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.
REGEXP_LIKE
The query above returns anyone who has a 2 in their Dept_No or anyone who has an 'La' in their Last_Name.
REGEXP_REPLACE
The query above replaces any zero with a one for Dept_No.
REGEXP_INSTR
REGEXP_INSTR allows you to search a string for a regular expression pattern. It returns an integer indicating the beginning or ending position of a matched substring, depending on the value of the return_option argument. If no match is found, the function returns 0. Above, we are searching the Last_Name column starting in position 1 and looking for the second occurrence of the letter n.
REGEXP_INSTR (1 of 4)
The REGEXP_SUBSTR is designed to extract a substring from a string using regular expression pattern matching.
REGEXP_INSTR Options (2 of 4)
The REGEXP_SUBSTR is designed to extract a substring from a string using regular expression pattern matching.
REGEXP_INSTR Options (3 of 4)
The REGEXP_SUBSTR is designed to extract a substring from a string using regular expression pattern matching.
REGEXP_INSTR Options (4 of 4)
The REGEXP_SUBSTR is designed to extract a substring from a string using regular expression pattern matching.
REGEXP_INSTR Example
The example above will get the first word in a string, including the space between TeraTom and books.
REGEXP_INSTR Example 2
The example above will get the second word in a string, including the space between 'are' and 'the'.