Chapter 25
Substrings and Positioning Functions
“It is not the position but the disposition.”
Table of Contents Chapter 25 – Substrings and Positioning Functions
– The CHARACTERS Command Counts Characters
– The CHARACTERS Command – Spaces can Count too
– Troubleshooting the CHARACTERS Command
– TRIM for Troubleshooting the CHARACTERS Command
– CHARACTERS and CHARACTER_LENGTH equivalent
– The TRIM Command trims both Leading and Trailing Spaces
– Trim and Trailing is Case Sensitive
– Trim and Trailing works if Case right
– Trim Combined with the CHARACTERS Command
– How to TRIM only the Trailing Spaces
– How to TRIM Trailing Letters
– A Visual Example of How to TRIM Trailing Letters
– How to TRIM Trailing Letters and use CHARACTER_Length
– How SUBSTRING Works with NO ENDING POSITION
– Using SUBSTRING to move Backwards
– How SUBSTRING Works with a Starting Position of Zero
– How SUBSTRING Works with a Starting Position of –1
– How SUBSTRING Works with an Ending Position of 0
– An Example using SUBSTRING, TRIM and CHAR Together
– SUBSTRING and SUBSTR are equal, but use different syntax
– The POSITION Command finds a Letters Position
– The POSITION Command is brilliant with SUBSTRING
– Quiz – Name that SUBSTRING Starting and For Length
– Answer to Quiz – Name that Starting and For Length
– Quiz – Find that SUBSTRING Starting Position
– Answer to Quiz – Find that SUBSTRING Starting Position
– Quiz – Find that SUBSTRING Starting FOR Length
– Answer to Quiz – Find that Starting FOR Length
– Quiz – Why Did only one Row Return
– Answer to Quiz – Why Did only one Row Return
– Four Concatenations Together
– Troubleshooting Concatenation
The CHARACTERS Command Counts Characters
The CHARACTERS command counts the number of characters . If ‘Tom’ was in the Employee_Table, his length would be 3
The CHARACTERS Command – Spaces can Count too
If ‘ To m’ was in the Employee_Table, his length would be 5. Yes, spaces do count as characters.
Troubleshooting the CHARACTERS Command
Last_Name is a CHAR(20) fixed length field.
When it comes to Characters, 20 would be the length of each and every name. That is because it has been set as a CHAR(20) in the table create syntax.
TRIM for Troubleshooting the CHARACTERS Command
Last_Name is a CHAR(20) fixed length field.
| Last_Name | C_Length |
| Jones | 5 |
| Smith | 5 |
| Smythe | 6 |
| Harrison | 8 |
| Chambers | 8 |
| Strickling | 10 |
| Reilly | 6 |
| Coffing | 7 |
| Larkins | 7 |
The TRIM command will trim off any spaces before and after the Last_name.
CHARACTERS and CHARACTER_LENGTH equivalent
Query 1
SELECT First_Name
,CHARACTERS(First_Name) AS C_Length
FROM Employee_Table ;
Query 2
SELECT First_Name
,CHARACTER_Length(First_Name) AS C_Length
FROM Employee_Table ;
These two queries will get you the SAME EXACT answer set in your report.
OCTET_LENGTH
Query 1
SELECT First_Name
,CHARACTERS(First_Name) AS C_Length
FROM Employee_Table ;
Query 2
SELECT First_Name
,CHARACTER_Length(First_Name) AS C_Length
FROM Employee_Table ;
Query 3
You can also use the OCTET LENGTH command. These three queries get the same exact answer sets! Query 2 and 3 are ANSI Standard.
The TRIM Command trims both Leading and Trailing Spaces
Query 1
SELECT Last Name
,Trim(Last_Name) AS No_Spaces
FROM Employee_Table ;
Query 2
SELECT Last Name
,Trim(Both from Last_Name) AS No_Spaces
FROM Employee Table ;
Both queries trim both the leading and trailing spaces from Last_Name.
Trim and Trailing is Case Sensitive
‘Billy’ does not TRIM the trailing ‘y’ because it was after a capitol ‘Y’'
For LEADING and TRAILNG, it IS case sensitive.
Trim and Trailing works if Case right
‘Billy‘ now TRIMs the trailing ‘y’ and the answer becomes ‘Bill’
For LEADING and TRAILNG, it IS case sensitive.
Trim Combined with the CHARACTERS Command
Query 1
SELECT Last_Name
,Characters(Trim(Last_Name)) AS No_Spaces
FROM Employee_Table ;
This will allow for the character count to only be 9 because both the leading and trailing spaces have been cut.
How to TRIM only the Trailing Spaces
Query 1
SELECT Last_Name
,Characters(Trim(trailing from Last_Name)) AS No_Spaces
FROM Employee_Table ;
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 also the beginning spaces.
How to TRIM Trailing Letters
If ‘Billy‘ was a First_Name and ‘Coffing ‘ is the Last_Name, what is the result be for No_Y and No_G?
A Visual Example of How to TRIM Trailing Letters
How to TRIM Trailing Letters and use CHARACTER_Length
The SUBSTRING Command
This is a SUBSTRING. What this does is start in position 2 and go for 3 positions, thus delivering the answer ‘ERA’.
How SUBSTRING Works
In this example, if ‘Tera-Tom‘ was the First_Name in the Employee_Table, we would start on the 2 character in the name and travel three characters. We'd take those characters and trim the rest and end up with ‘era'.
How SUBSTRING Works with NO ENDING POSITION
If you don't tell the Substring the end position, it will go all the way to the end.
Using SUBSTRING to move Backwards
If ‘Tera-Tom‘ was the First_Name in the Employee_Table what would be his result be for Quiz ?
How SUBSTRING Works with a Starting Position of Zero
Since you are starting in the 0 position, you have to count a space in front of the name. Then you count over six from there.
How SUBSTRING Works with a Starting Position of –1
If ‘Tera-Tom‘ was the First_Name in the Employee_Table what would be his result be for Quiz? TERA. Since you are starting in the –1 position, you have to count two spaces in front of the name. Then you count over six from there.
How SUBSTRING Works with an Ending Position of 0
If ‘Tera-Tom‘ was the First_Name in the Employee_Table what would be his result be for Quiz? The result would be nothing.
An Example using SUBSTRING, TRIM and CHAR Together
| Last_Name | Letters |
| Jones | es |
| Smith | th |
| Smythe | he |
| Harrison | on |
| Chambers | rs |
| Strickling | ng |
| Reilly | ly |
| Coffing | ng |
| Larkins | ns |
You can also combine substring and TRIM. If ‘Coffing ‘ was the Last_Name in the example above what would be his result for What_Letters?
SUBSTRING and SUBSTR are equal, but use different syntax
Both queries above are going to yield the same results! SUBSTR is just a different way of doing a substring.
The POSITION Command finds a Letters Position
SELECT Last_Name
,Position('e' in Last_Name) AS Find_The_E
FROM Employee_Table
WHERE Last_Name IN ('Smith', 'Jones'),
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. There is no ‘e’ in Smith. If there are two ‘e's only the first is reported.
The POSITION Command is brilliant with SUBSTRING
SELECT Dept_No
,Department_Name as Depty
,SUBSTRING(Depty FROM 1 FOR POSITION(' ' IN
Department_Name) –1) as Word1
FROM Department_Table;
What was the starting position of the Substring in the above query? It was one. The ending position (FOR length) was calculated to look for the first space and then subtract 1. So for “Research and Develop” the starting position was one and For 9-1 = 8.
Quiz – Name that SUBSTRING Starting and For Length
SELECT Dept_No
,Department_Name as Depty
,SUBSTRING(Depty FROM 1 FOR POSITION(' ' IN Department_Name) –1) as Word1
FROM Department_Table;
The FOR Length is calculated by finding the length up to the first SPACE and then subtracting 1.
Marketing (FROM FOR )
Research and Develop (FROM FOR )
Sales (FROM FOR )
Customer Support (FROM FOR )
Human Resources (FROM FOR )
Fill in the number for the FROM and the FOR numbers above for each row. Next page!
Answer to Quiz – Name that Starting and For Length
SELECT Dept_No
,Department_Name as Depty
,SUBSTRING(Depty FROM 1 FOR POSITION(' ' IN Department_Name) –1) as Word1
FROM Department_Table;
The FOR Length is calculated by finding the length up to the first SPACE and then subtracting 1.
Marketing (FROM 1 FOR 9)
Research and Develop (FROM 1 FOR 8)
Sales (FROM 1 FOR 5)
Customer Support (FROM 1 FOR 8)
Human Resources (FROM 1 FOR 5)
The FOR was calculated in the POSITION Subquery.
Quiz – Find that SUBSTRING Starting Position
SELECT DISTINCT Department_Name as Dept_Name
,SUBSTRING(Department_Name FROM
POSITION(‘ ‘ IN Department_Name) +1) as Word2
FROM Department_Table
WHERE POSITION(' ' IN trim(Department_Name)) >0;
What is the Starting Position here?
What is the Starting position of the Substring in the above query? Hint: This only looks for a Dept_Name that has two words or more.
Answer to Quiz – Find that SUBSTRING Starting Position
SELECT DISTINCT Department_Name as Dept_Name
,SUBSTRING(Department_Name FROM
POSITION(‘ ‘ IN Department_Name) +1) as Word2
FROM Department_Table
WHERE POSITION(' ' IN trim(Department_Name)) >0;
The Starting Position is calculated by finding the length up to the first SPACE and then adding 1.
Customer Support (FROM 10)
Human Resources (FROM 7)
Research and Develop FROM 10)
What is the Starting position of the Substring in the above query? See above!
Quiz – Find that SUBSTRING Starting FOR Length
SELECT DISTINCT Department_Name as Dept_Name
,SUBSTRING(Department_Name FROM
POSITION(' ' IN Department_Name) +1) as Word2
FROM Department_Table
WHERE POSITION(' ' IN trim(Department_Name)) >0;
What is the FOR Position length here?
What is the FOR Length of the Substring in the above query?
Answer to Quiz – Find that Starting FOR Length
SELECT DISTINCT Department_Name as Dept_Name
,SUBSTRING(Department_Name FROM
POSITION(' ' IN Department_Name) +1) as Word2
FROM Department_Table
WHERE POSITION(' ' IN trim(Department_Name)) >0;
The Starting Position is calculated by finding the length up to the first SPACE and then adding 1.
Customer Support (FROM 10 FOR “go to the end”)
Human Resources (FROM 7 FOR “go to the end”) Research and Develop FROM 10 FOR “go to the end”)
What is the FOR Position Length of the Substring in the above query? Since there was no FOR statement the Substring defaults to the end. So this query finds the first space and then adds 1 to the Starting Position and allows the rest to default to the end.
Quiz – Why Did only one Row Return
SELECT Department_Name
,SUBSTRING(Department_Name from
POSITION(' ' IN Department_Name) + 1 +
POSITION(' ' IN SUBSTRING(Department_Name
FROM POSITION(' ' IN Department_Name) + 1))) as Third_Word
FROM Department_Table
WHERE POSITION(' ' IN
TRIM(Substring(Department_Name from
POSITION(' ' IN Department_Name) + 1)))> 0
Why did only one row come back?
Answer to Quiz – Why Did only one Row Return
SELECT Department_Name
,SUBSTRING(Department_Name from
POSITION(' ' IN Department_Name) + 1 +
POSITION(' ' IN SUBSTRING(Department_Name
FROM POSITION(' ' IN Department_Name) + 1))) as Third_Word
FROM Department_Table
WHERE POSITION(' ' IN
TRIM(Substring(Department_Name from
POSITION(‘ ‘ in Department_Name) + 1)))> 0
Why did only one row come back? It's the Only Department Name with three words. The SUBSTRING and the WHERE clause both look for the first space and if they find it then look for the second space. If they find that add 1 to it and their Starting Position is the third word. There is no FOR position so it defaults to “go to the end”.
Concatenation
See those || ? 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 combines the first name, then a single space, and then 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 we have a single space, then we have the First Initial of the First Name and then we have a Period.
Troubleshooting Concatenation
SELECT First_Name
,Last_Name
,TRIM (Last_Name)| |' ‘ | | Substring(First_Name, 1, 1)||‘.’
as Last_Name_1st
FROM Employee_Table
WHERE First_Name = ‘Squiggy' ;
ERROR
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.