Introduction
String functions let you clean, transform, and combine text data directly inside a query, without needing to pull data into an application first. Common tasks include joining first and last names, extracting a substring, changing case, trimming whitespace, and finding the length of a value. While exact function names vary slightly across databases (MySQL, PostgreSQL, SQL Server), the core set covered here works in standard SQL or with minor syntax adjustments.
Cricket analogy: Just as a scorer cleans up a raw match log, trimming stray marks and joining a player's first and surname for the official scorecard, SQL string functions clean, transform, and combine text directly inside a query.
Syntax
SELECT CONCAT(str1, str2, ...) AS combined,
UPPER(column) AS upper_case,
LOWER(column) AS lower_case,
TRIM(column) AS trimmed,
LENGTH(column) AS char_length,
SUBSTRING(column, start, length) AS piece,
REPLACE(column, 'old', 'new') AS replaced
FROM table_name;Explanation
CONCAT joins two or more strings into one; some databases also support the || operator for concatenation. UPPER and LOWER convert text to uppercase or lowercase respectively, which is useful for case-insensitive comparisons. TRIM removes leading and trailing whitespace (some dialects offer LTRIM/RTRIM for one side only). LENGTH (or LEN in SQL Server) returns the number of characters in a string. SUBSTRING extracts a portion of a string starting at a given position for a given length. REPLACE swaps every occurrence of a target substring with a replacement string.
Cricket analogy: CONCAT joining a player's first and last name for the scoreboard, UPPER making a team name display in caps, and TRIM removing stray spaces from a hastily typed name, are all everyday scorer tasks mirroring SQL's string functions.
Example
-- customers table
-- customer_id | first_name | last_name | email
-- 1 | ' Alice' | 'Nguyen' | 'ALICE.N@EMAIL.COM'
SELECT customer_id,
CONCAT(TRIM(first_name), ' ', last_name) AS full_name,
LOWER(email) AS normalized_email,
LENGTH(TRIM(first_name)) AS first_name_length,
SUBSTRING(last_name, 1, 3) AS last_name_prefix,
REPLACE(email, 'EMAIL.COM', 'example.com') AS fixed_domain
FROM customers;Output
full_name = 'Alice Nguyen' (TRIM removes the leading spaces before concatenation), normalized_email = 'alice.n@email.com', first_name_length = 5 (the length of 'Alice' after trimming), last_name_prefix = 'Ngu' (first 3 characters of 'Nguyen'), and fixed_domain = 'ALICE.N@example.com' (only the matched substring 'EMAIL.COM' is replaced, case-sensitively in most databases).
Cricket analogy: The trimmed and concatenated full_name 'Alice Nguyen', with a shortened prefix 'Ngu', mirrors a scorer cleaning up a hastily entered scoresheet name and abbreviating it for a compact scoreboard display.
Key Takeaways
- CONCAT (or the || operator) joins multiple strings into one value.
- UPPER and LOWER normalize text case, useful for case-insensitive comparisons.
- TRIM removes leading/trailing whitespace; LTRIM/RTRIM target one side.
- SUBSTRING extracts part of a string given a start position and length.
- REPLACE performs a case-sensitive find-and-replace across the whole string in most databases.
- Function names differ slightly by vendor (e.g., LEN in SQL Server vs LENGTH elsewhere).
Practice what you learned
1. What does TRIM(' Hello ') return?
2. Which function converts a text value to all uppercase letters?
3. Given last_name = 'Robinson', what does SUBSTRING(last_name, 1, 3) return?
4. What is the purpose of the REPLACE function in SQL?
Was this page helpful?
You May Also Like
Date and Time Functions in SQL
Work with dates using NOW/CURRENT_DATE, DATE_ADD/DATE_SUB, DATEDIFF, and EXTRACT to filter and calculate time-based data.
SELECT Statement Basics
Learn how to retrieve data from a table using the SELECT statement, the foundation of every SQL query.
Filtering with WHERE
Use the WHERE clause to filter rows returned by a query based on specified conditions.