Chapter 11 – 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 LENTH 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 Doesn't Count Trailing Spaces
Last_Name |
Lnth |
Chambers |
8 |
Coffing |
7 |
Harrison |
8 |
Jones |
5 |
Larkins |
7 |
Reilly |
6 |
Smith |
5 |
Smythe |
6 |
Strickling |
10 |
The LENGTH command counts characters, but it also auto-trims the ending spaces at the end of each last name.
UPPER and LOWER Commands
Upper converts text to uppercase and Lower converts text to lowercase.
Using the LOWER Command
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.
A LOWER Command Example
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. Above, we compare a LOWER 'ABCDE' = 'abcde' and they are now equivalent because we have lowercased the 'ABCDE'.
Using the UPPER 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.
An UPPER Command Example
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. Above, we compare a string of 'ABCDE' = UPPER 'abcde' and they are now equivalent because we have uppercased the 'abcde'.
Non-Letters Are Unaffected by UPPER and LOWER
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.
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_REPLACE
The query above replaces any zero with a one for Dept_No.
Concatenation
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 lastly the last name to get a new column called 'Full name'.
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.
SUBSTRING and SUBSTR are equal, but use different syntax
Query 1 with Substring
SELECT First_Name,
SUBSTRING(First_Name, 2, 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 just uses a different keyword than SUBSTRING. Both have two parameters, which are the starting position of the string and the number of characters to utilize.
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
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’. This shows 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
In our example above, we start in position 3, but we go for zero positions. The result is nothing is delivered in the column we aliased WhatsUp. That is what’s up!
An Example using SUBSTRING and LENGTH Together
SELECT Last_Name
,SUBSTRING(Last_Name,
LENGTH(Last_Name) -1, 2) AS Last2Letters
FROM Employee_Table;
The SQL above brings back the last two letters of each Last_Name even though the last names are of different length. We use the length command to count the characters in the Last_Name. Then we subtract1 from the length to get a starting position that is the second from the last letter. We then go for an ending length of two.
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.
The Context_Ngrams Function
The context_ngrams function estimates the top-n most frequent n-grams that fit into the particular wording. The second parameter specifies a string of words that specify the positions of the n-gram elements, with a null value standing in for a 'blank' that must be filled by an n-gram element. Above, we are looking for the word dedicated and the two-words following.
Sentences Function
[["Abraham","Lincoln"]][["Gettysburg","Address"]]
[["Four","score","and","seven","years","ago","our","fathers","brought","forth","on"
,"this","continent","a","new","nation","conceived","in","liberty","and","dedicated",
"to","the","proposition","that","all","men","are","created","equal"],["Now","we","a
re","engaged","in","a","great","civil","war","testing","whether","that","nation","or"
,"any","nation","so","conceived","and","so","dedicated","can","long","endure"],["
We","are","met","on","a","great","battlefield","of","that","war"],["We","have","co
me","to","dedicate","a","portion","of","that","field","as","a","final","resting","place
","for","those","who","here","gave","their","lives","that","that","nation","might","li
ve"],["It","is","altogether","fitting","and","proper","that","we","should","do","this"
],["But","in","a","larger","sense","we","can","not","dedicate","we","can","not . . .
The sentences function splits a string into an array of sentences where each sentence is an array of words. Each word is separated within brackets [ ]. Each beginning and ending bracket [ begins a new sentence]. Punctuation (periods, commas etc.,) are stripped away. The 'language' and 'country' are optional, but if specified, need to be a two-letter ISO-639 language code ('en' for English) and a two-letter ISO-3166 code ('us' for United States).
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.
Explode Ngrams Sentences to Find the 5 Most Popular Words
This query will find the top 5 single words from the column called wording in the table named Great_Speeches. It will list their frequency.
Explode Ngrams Sentences to Find the 5 Most Two-Words
This query will find the top 5 double word combinations from the column called wording in the table named Great_Speeches. It will list their frequency. The two-words are consecutive word combinations.
Explode Ngrams Sentences for the Top 5 Trigrams
This query will find the top 5 double word combinations from the column called wording in the table named Great_Speeches. It will list their frequency. The two-words are consecutive word combinations.
Explode Ngrams Sentences Finding Words Following a Phrase
This query will find the top 3-word combinations (called a trigram), from the column called wording in the table named Great_Speeches. It will list their frequency.
Explode Ngrams Sentences Finding Words Following a Phrase
This query will find the top 5 words that follow the phrase "for us" from the column called wording in the table named Great_Speeches. It will list their frequency. Although we were looking for the top 5, we only found two instances of the phrase "for us". The words "the" and "to" followed the phrase "for us".