Transforming with String-Text Functions
Welcome back to another SQL tutorial!
Today, we will learn commonly used string-text functions which are very handy for data cleaning.
LOWER(), UPPER()
returns all the letters in uppercase and returns all the letters in lowercase.
Syntax
Example
| product_name_upper | product_name_lower |
|---|---|
| APPLE | apple |
LENGTH()
returns the length of the string.
Syntax
Example
| name_length1 | name_length2 |
|---|---|
| 6 | 7 |
LEFT(), RIGHT()
function returns the first characters in the string and the function returns the last characters in a string.
Syntax
Example
| left_string | right_string |
|---|---|
| App | ple |
TRIM(), LTRIM(), RTRIM(), BTRIM()
function removes spaces from a string.
Here's 3 more functions to handle string trimming.
- function removes spaces by default, or predefined characters from the beginning of a string.
- function removes spaces by default, or predefined characters from the end of a string.
- function is the combination of the and functions.
Syntax
Example
| trim_1 | trim_2 | left_trim | right_trim |
|---|---|---|---|
| Apple | Apple | ple | Appl |
CONCAT()
function is used to concatenate two or more strings into one.
Syntax
Example
| beverage | full_name | |
|---|---|---|
| vanilla milkshake | nick@datalemur.com | Alice Bell |
SUBSTRING()
function returns a part of a string based on the defined starting point and length.
Syntax
Example
| substring_1 | substring_2 |
|---|---|
| Hello Wo | orld |
For , the starting point is 1 and ends at 8, hence it starts from "H" and ends at "o" returning "Hello Wo".
For , the starting point is 8 and as there is no length specified, SQL defaults the endpoint at the last letter, hence it returns "orld".
Summing Up
These functions may be simple, but they are fundamental to the core of database querying so make sure that you know them well.
Next Lesson
Handling NULL Values