Learn SQL for Developers/

...

/

String Functions

String Functions

Learn about string functions in SQL.

We'll cover the following...

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:

Press + to interact
SELECT CONCAT(string1, string2, string3,...) FROM TableName;

Suppose we want to display each customer’s name and email address in a single field called ContactInfo, we can achieve it as follows:

Press + to interact
-- Definition for the Customers table
-- CREATE TABLE Customers (
--     CustomerID INT PRIMARY KEY  AUTO_INCREMENT,
--     CustomerName VARCHAR(50) NOT NULL,
--     Email VARCHAR(50),
--     Phone VARCHAR(15),
--     Address VARCHAR(100)
-- );
-- ------------------------------------------------------------------------------
SELECT CustomerName, Email, CONCAT(CustomerName, ' - ', Email) AS ContactInfo
FROM Customers;

We have inserted a hyphen (-) and a space as separators between CustomerName and Email. 

It is important to note that ...