String Functions

Explanation

String functions let you reshape text right inside a query — tidy up names, build labels, extract parts of a code, or measure length. The workhorses you'll reach for constantly:

• UPPER / LOWER — change letter case
• LENGTH — number of characters
• CONCAT (or ||) — join strings together
• SUBSTRING / SUBSTR — pull out a slice
• TRIM — strip leading/trailing spaces

These pair naturally with LIKE and CASE to clean and categorize text.

Syntax

SELECT
  UPPER(first_name) AS upper_name,
  LENGTH(last_name) AS name_len,
  CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

Interactive Example

Build a clean display name and measure name lengths. Then extract the first letter of each last name.

Common Mistakes

• Off-by-one in SUBSTRING. In standard SQL the start position is 1, not 0.
• Assuming case-insensitive matching. Normalize with UPPER/LOWER when it matters.
• Wrapping indexed columns in functions. WHERE LOWER(email) = ... may skip an index; consider an expression index.

Best Practices

• Use || or CONCAT to assemble labels instead of doing it in app code.
• Trim and normalize user input on the way in to avoid trailing-space bugs.
• Keep transformations in the query only when you need them for display or filtering.

Practice Question

Write a query that returns each employee's email in uppercase and a column name_len equal to the length of first_name || last_name (no space), ordered by name_len descending.

Summary

String functions reshape text in queries: UPPER/LOWER for case, LENGTH for size, CONCAT/|| for joining, and SUBSTRING for slicing. They integrate with LIKE and CASE for clean, predictable text handling.

FAQ