1. Parsing string and character data
Next up we will learn about string functions in PostgreSQL that allow us to parse and manipulate text data. We will also learn how to combine and nest functions to provide additional capabilities.
2. Determining the length of a string
First, let's look at the CHAR_LENGTH function. The CHAR_LENGTH function can be used to determine the number of characters in a string. CHAR_LENGTH accepts a string as an input and returns the number of characters in the string as an integer for the output.
In this example, we see the CHAR_LENGTH function used on the title column of the film table in the DVD Rental database.
3. Determining the length of a string
LENGTH is analogous to CHAR_LENGTH, accepts the same parameter and returns the same result as you can see in this example. These two functions can be used interchangeably depending on your preference.
4. Finding the position of a character in a string
The POSITION function returns an integer which represents the number of characters from left to right before the search string is located.
Looking at the customer table we can find the position of the at sign in the email column.
5. Finding the position of a character in a string
STRPOS is analogous to POSITION with a slightly different syntax as you see in this example.
6. Parsing string data
Now let's look at some functions that will help you parse strings into substrings. The LEFT function allows you to extract the first "n" characters of a string. In this example, we are going to extract the first fifty characters of the description column from the film table in our DVD Rental database.
7. Parsing string data
The RIGHT function is very similar to LEFT but as you might expect it extracts the last "n" characters of a string.
8. Extracting substrings of character data
SUBSTRING allows us to do pretty much exactly what its name implies - extract a substring from text data. The substring functions takes 3 parameters. The first is the source string or column, in this example the description column from the film table. This is followed by an integer representing the starting position of the source string or in this case the number 10. Finally, we include another integer to specify the length of the substring that we want to extract. In this case, the number 50.
9. Extracting substrings of character data
SUBSTRING can be combined with other functions to provide additional capabilities. In this example, we can extract the text from the left side of the at sign in an email address using a slightly different set of parameters in the SUBSTRING function. The first parameter remains the same, but the second parameter is replaced with the FROM keyword followed by an integer representing the starting position and the third parameter includes the FOR keyword followed by an integer representing the ending position. In this example we use the POSITION function as the second parameter in the SUBSTRING function.
10. Extracting substrings of character data
Now we can use a different technique if we want to extract the characters to the right of the at sign in the email column. In this example, we use the POSITION function as the second parameter of SUBSTRING to determine the starting position in the string and the CHAR_LENGTH to determine the last position which is a nice trick for determining the last position of a string.
The POSITION function will return the integer value of the position of the at sign in the string. To exclude the at sign from the result, we need to add one to the starting position.
11. Extracting substrings of character data
SUBSTR is analogous to SUBSTRING but only allows for the parameters to be separated by commas and does not allow for the alternative syntax with the FROM and FOR keywords.
12. Let's practice!
Now it's your turn! Let's practice using these functions in the exercises.