Truncating and padding string data
1. Truncating and padding string data
Next, we are going to take a look at how to truncate and replace or overwrite characters in a string.2. Removing whitespace from strings
The first function that we'll look at is the TRIM function. The TRIM function will remove characters from either the start or end of the string or both and accepts three parameters. The first parameter is optional and specifies whether you want to remove characters from the beginning or end of a string or both. If this parameter is omitted, the default value is both. The second parameter which is also optional specifies the characters to be removed from the string. If this parameter is omitted, the default value is a blank space. And finally the third parameter is the string that you wish to trim.3. Removing whitespace from strings
Most of the time you'll use this function without the first two parameters and just pass a string as a single parameter. This default behavior will remove all whitespace from the beginning and end of the string as you see in this example.4. Removing whitespace from strings
The LTRIM and RTRIM functions are analogous to TRIM but only remove characters from either the beginning OR the end of the string, not both. Much like TRIM, you'll use these functions with their default behavior to truncate whitespace. In this example, we see that LTRIM removes only the spaces at the beginning of the word padded but leaves the spaces at the end of the string.5. Removing whitespace from strings
And you'll see the opposite result from RTRIM in this example.6. Padding strings with character data
The function LPAD appends a character or string to another string by a specified number of characters. This is useful when you need a field to be the same length and want to pad the string with a certain character like a space or a tab. In this example, we are padding the word 'padded' with the hash character so that the string returned has a character length equal to ten.7. Padding strings with whitespace
If you omit the third parameter in the LPAD function as you see in this example, the string will be padded with a space character by default. And when the length parameter is less than the original length of the string as you see here, the result returned will be truncated.8. Padding strings with whitespace
The RPAD function is analogous to LPAD but will pad the string with characters to the right.9. Let's practice!
Alright, now it's time to practice these functions with the exercises.Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.