In the medium post, you will learn how to work with text strings using some powerful functions in SQL. I will be going through quite a few ideas here but I am sure you can follow along easily.
Working with String Variables.
We will be going through how to concatenate, use substrings, trim, upper function and the lower function.
Concatenations.
Let’s say that you want to link together the first and last names of your customers to make a new column called ‘FullName’.
We can do this using the following code:
SELECT FirstName,
LastName,
FirstName || LastName ||
FROM CustomersYou notice that we use the ‘|’ or ‘pipe’ key.
Trimming Strings.
You can use the trimming function to trim leading space or trailing space from a column.
We could do this in a few ways:
- TRIM
- RTRIM (Right)
- LTRIM (Left)
SELECT TRIM(' You Are The Best ') AS TrimmedString;Substring.
This function allows you to pull apart a portion of a string. We can actually specify this with a lot of control.
SUBSTR(string_name, string position,
number of characters to be returned);Let’s add in some actual information to make sense of what I just wrote for you.
SELECT first_name, SUBSTR(first_name,2,3)
FROM employees
WHERE department_id=60;The first number in the SUBSTR function tells us at what position we should start to make our substring. The second tells us how many positions you want to include in our string. In the example above, I chose 3.
Upper and Lower.
I will wrap up the lesson with one more quick way to clean up your data.
It is good practice to change all strings to UPPER, LOWER or UCASE for consistency. Though the first two seem like more commonly used options.
SELECT UPPER(column_name)
FROM table_name;
SELECT LOWER(column_name)
FROM table_name;
SELECT UCASE(column_name)
FROM table_name;
