String Functions

Imagine we are tasked with creating a product catalog for an online store. Product names, categories, and descriptions often need formatting, concatenation, or modification. Similarly, when building a customer report, we might need to display a person’s name in uppercase or extract the first few letters of a product’s name to create a code. These tasks rely on string functions, which allow us to combine, modify, and analyze text fields, making our data more meaningful and user-friendly.

Let's understand how to manipulate and analyze text data in SQL using string functions, enabling us to clean, format, and extract meaningful insights from textual data. We will aim to:

• Understand why string functions are important in real-world SQL scenarios.

• Learn how to concatenate strings using CONCAT.

• Extract parts of strings using SUBSTRING.

• Replace text within strings using REPLACE.

• Calculate the length of a string with CHAR_LENGTH.

• Change text case with UPPER and LOWER.

String functions in SQL

Let's dive into exploring string functions and how these can be used in the following.

Concatenating strings using CONCAT

We often need to merge two or more text fields, such as combining first and last names or merging labels with numeric values. The CONCAT function joins multiple strings into a single string. We list the strings or columns inside CONCAT, and SQL returns them as one continuous string. The syntax of using CONCAT is as follows: