3.4 — Text Manipulation

What You'll Learn

• How to use SQL functions to work with text
• Concatenation, substring, and case conversion
• Excel text functions vs. SQL

Concatenating Text

In Excel, you use & or CONCATENATE. In SQL, use || or CONCAT() (syntax may vary by database).

SQL (standard, all databases support):

SELECT Product, Amount, Product || '-' || Amount AS Product_Amount
FROM sales;

SQL (MySQL, Databricks for example):

SELECT CONCAT(Product, ' - ', Amount) AS Product_Amount
FROM sales;

Extracting Parts of Text

Excel:
=LEFT(Product, 3) or =MID(Product, 2, 3)

SQL:

SELECT Product, SUBSTR(Product, 1, 3) AS Product_Prefix
FROM sales;

• LEFT(text, n) gets the first n characters.
• SUBSTRING(text, start, length) extracts part of the text.

Other useful text functions

• UPPER(text) : majuscules
• LOWER(text) : minuscules
• TRIM(text) : supprime les espaces

Key Points

• SQL has many text functions similar to Excel.
• Syntax may vary slightly between databases.

Next Steps

Next, you’ll learn how to categorize data using CASE—just like using IF or IFS in Excel.