Welcome back to another SQL tutorial!
Today, we will learn commonly used string-text functions which are very handy for data cleaning.
returns all the letters in uppercase and returns all the letters in lowercase.
Syntax
Example
product_name_upper | product_name_lower |
---|---|
APPLE | apple |
returns the length of the string.
Syntax
Example
name_length1 | name_length2 |
---|---|
6 | 7 |
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 |
function removes spaces from a string.
Here's 3 more functions to handle string trimming.
Syntax
Example
trim_1 | trim_2 | left_trim | right_trim |
---|---|---|---|
Apple | Apple | ple | Appl |
function is used to concatenate two or more strings into one.
Syntax
Example
beverage | full_name | |
---|---|---|
vanilla milkshake | nick@datalemur.com | Alice Bell |
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".
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