Chapter 12 – Substring and Positioning Functions
“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 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
SELECT 'T o m' AS First_Name
,CHARACTERS('T o m') AS Lnth
Answer Set |
|
First_Name |
Lnth |
T o m |
5 |
If ‘T o m’ was in the Employee_Table, his length would be 5. Yes, spaces do count as characters.
The CHARACTERS 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 |
The CHARACTERS command brings back a length of 20 every time for a Char(20) data type because of the spaces. Turn the page and we will explain further.
Troubleshooting the CHARACTERS Command
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 |
Lnth |
Chambers |
8 |
Coffing |
7 |
Harrison |
8 |
Jones |
5 |
Larkins |
7 |
Reilly |
6 |
Smith |
5 |
Smythe |
6 |
Strickling |
10 |
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
SELECT First_Name
,Octet_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 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 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.
Trim Combined with the CHARACTERS Command
SELECT ' Rodriquez '
,Characters (Trim (' Rodriquez ')) AS No_Spaces ;
' Rodriquez ' |
No_Spaces |
Rodriquez |
9 |
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
SELECT ' Rodriquez '
,Characters (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.
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.
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!
SUBSTRING and SUBSTR are equal, but use different syntax
Query 1 with Substring
SELECT First_Name,
SUBSTRING(First_Name FROM 2 for 3) AS Quiz
FROM Employee_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 character length.
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 Substring the end position, it will go all the way to the end.
Using SUBSTRING to move Backwards
First_Name |
Before1 |
Squiggy |
Squig |
John |
John |
Richard |
Richa |
Herbert |
Herbe |
Mandee |
Mande |
Cletus |
Cletu |
William |
Willi |
Billy |
Billy |
Loraine |
Lorai |
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.
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
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. Since we didn’t give an ending position in our substring it defaulted to the end.
The POSITION Command finds a Letters Position
SELECT Last_Name
,Position ('e' in Last_Name) AS Find_The_E
,Position ('f' in Last_Name) AS Find_The_F
FROM Employee_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.
The POSITION Command is brilliant with SUBSTRING
SELECT Dept_No ,Department_Name as Depty
,SUBSTR (Depty ,1 , POSITION(' ' IN Department_Name) -1)
as Word1
FROM Department_Table;
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 – 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;
Dept_Name |
Word2 |
Customer Support |
Support |
Human Resources |
Resources |
Research and Develop |
and Develop |
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;
Dept_Name |
Word2 |
Customer Support |
Support |
Human Resources |
Resources |
Research and Develop |
and Develop |
What is the Starting Position here?
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!
Using the SUBSTRING to Find the Second Word On
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;
Dept_Name |
Word2 |
Customer Support |
Support |
Human Resources |
Resources |
Research and Develop |
and Develop |
Notice we only had three rows come back. That is because our WHERE looks for only Department_Name that has multiple words. Then, notice that our starting position of the Substring is a subquery that looks for the first space. Then, it adds 1 to the starting position, and we have a starting position for the 2nd word. We don’t give a FOR length parameter, so it goes 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
Dept_Name |
Third_Word |
Research and Develop |
Develop |
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
Dept_Name |
Third_Word |
Research and Develop |
Develop |
It has 3 words
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, they 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 combined 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
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.
Imbedded Services Functions
A database called TD_SYFNLIB can be created through DBA DIP script processing.
TD_SYFNLIB will hold functions that can be used to convert data from one data type to another data type.
You can invoke a function without fully qualifying it with the database name TD_SYFNLIB. You can also fully qualify it with the TD_SYFNLIB database.
Here is a list of those functions:
The Imbedded Services Functions can be used to convert data from one data type to another.
Imbedded Services Functions and Their Descriptions
| FROM_BYTES | A sequence of bits are encoded into a sequence of characters representing its encoding. |
| TO_BYTES | A sequence of characters are encoded into a sequence of bits representing its encoding. |
| TO_NUMBER | Converts a string_expr to a NUMBER data type. |
| TO_CHAR (numeric) | Converts numeric_expr to a character string. |
| TO_CHAR (DateTime) | Converts date_timestamp_value to a character string. |
| TO_DATE | Converts string_expr to a Date. |
| TO_TIMESTAMP | Converts string_expr to a TIMESTAMP data type. |
| TO_TIMESTAMP_TZ | Converts string_expr to a TIMESTAMP WITH TIMEZONE data type. |
| TO_YMINTERVAL | Converts string_value specified in an INTERVAL YEAR(4) TO MONTH value. |
| TO_DSINTERVAL | Converts string_value specified in an INTERVAL DAY(4) TO SECOND(6) value. |
| NUMTODSINTERVAL | Converts numeric_value into an INTERVAL DAY(4) TO SECOND(6) value. |
| NUMTOYMINTERVAL | Converts numeric_value into an INTERVAL YEAR(4) TO MONTH value. |
The Imbedded Services Functions can be used to convert data from one data type to another. The chart above lists the description for each.
Imbedded Services Functions Example With To_Date
| TO_DATE | Converts string_expr to a Date. |
SELECT
Date_Column as char12dt
,TD_SYSFNLIB.TO_DATE(Order_Date, 'Mon dd, yyyy') as Convert1
FROM SQL_Class.Order_Table
WHERE Order_Date = '1998-05-04'
Char12dt |
Convert1 |
May 05, 1998 |
98/05/04 |
The Imbedded Services Functions can be used to convert data from one data type to another. Above is an example.