Functions for string transformation

1. Functions for string transformation

The functions used to manipulate strings are pretty fun and you can do a lot of things with them. In this video, I will show you some of the most common functions used to transform a string.

2. LOWER() and UPPER()

The two simplest functions for string transformation are LOWER() and UPPER(). Both of them receive a string as parameter. LOWER() returns the same string, with all characters converted to lowercase and UPPER() does the same, but converts everything to uppercase.

3. LOWER() and UPPER() example

You can see in this query how to use these functions. In the results, we retrieve the country as it is stored in the database, then the same country in lowercase and in uppercase.

4. LEFT() and RIGHT()

LEFT() and RIGHT() receive two parameters: the string you're working with and the number of characters you need from it. LEFT() will return that number of characters from the beginning of the string and RIGHT() will do the same, starting with the end of the string.

5. LEFT() and RIGHT() example

Let's say you want to retrieve the prefix of each country, consisting in the first 3 letters and also the domain for each email address. This is how the query looks. You can see in the results that the column "country_prefix" contains the first 3 letters of each country and the "email_domain" contains the last 4 letters from the email.

6. LTRIM(), RTRIM(), and TRIM()

LTRIM() and RTRIM() remove the blank characters from a string: LTRIM() from the beginning and RTRIM() from the end. SQL Server 2017 introduced the TRIM() function, which removes the blank characters from both the beginning and the end of the string.

7. REPLACE()

Another interesting function is REPLACE(). This one receives three parameters: the expression in which we're performing the search, the expression we're searching for and then the replacement. In this example, the expression where I'm doing the search is "I like apples, apples are good" and I want to replace every occurrence of the word "apple" with "orange". You can see in the result that this is exactly what happens when applying the REPLACE() function with the correct parameters.

8. SUBSTRING()

If you want to return only a part of a string, then SUBSTRING() is the function you're looking for. This function needs three parameters: the expression from which you want to extract the substring, the starting position of your substring and the total number of characters you want to return. In this query, the source string is "123456789", from which I will extract 3 characters, starting with the fifth position. Since the counting starts from 1, and not from 0, the fifth character is 5 so the 3 characters returned are: "567".

9. Let's practice!

It's now time to do some practical examples on your own with these functions. You will see how fun it is to use them and how much they can improve your queries.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.