logo

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_upperproduct_name_lower
APPLEapple

LENGTH()

returns the length of the string.

Syntax


Example


name_length1name_length2
67

LEFT(), RIGHT()

function returns the first characters in the string and the function returns the last characters in a string.

Syntax


Example


left_stringright_string
Appple

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_1trim_2left_trimright_trim
AppleApplepleAppl

CONCAT()

function is used to concatenate two or more strings into one.

Syntax


Example


beverageemailfull_name
vanilla milkshakenick@datalemur.comAlice Bell

SUBSTRING()

function returns a part of a string based on the defined starting point and length.

Syntax


Example


substring_1substring_2
Hello Woorld

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