Home/Learn/SQL/String Functions

String Functions

Beginner
DML & Querying

By the AiCanCode.org team · How we write and check our content

SQL string functions (CONCAT, SUBSTRING, TRIM, UPPER, LOWER, REPLACE, LENGTH, REGEXP) manipulate text columns for data cleaning, formatting, and search operations.

Overview

String functions are evaluated row-by-row during the SELECT or WHERE phase. Using a function on an indexed column in a WHERE predicate typically prevents index use — always try to rewrite to use the indexed form or create an expression index. CONCAT is ANSI-standard; MySQL also supports the || operator (PostgreSQL uses || natively). TRIM removes leading/trailing whitespace (common in ETL pipelines). SUBSTRING (or SUBSTR) extracts a portion of a string. REGEXP (MySQL) and ~ (PostgreSQL) allow regular expression matching. String functions are often misused to work around bad data modelling — storing CSV in a column and using string splitting, for example, is a red flag.

Core String Functions

These functions cover the vast majority of real-world text manipulation tasks in reporting queries, data migrations, and API response formatting.

SQL — core string functions (PostgreSQL / MySQL)
-- PostgreSQL / MySQL string functions

-- CONCAT: join strings (CONCAT_WS includes separator, skips NULLs)
SELECT CONCAT(first_name, ' ', last_name)             AS full_name FROM employees;
SELECT CONCAT_WS(', ', city, state, country)          AS address   FROM users;

-- UPPER / LOWER: normalise for case-insensitive comparison
SELECT id FROM users WHERE LOWER(email) = 'alice@example.com';
-- Better: use a case-insensitive collation or expression index

-- TRIM / LTRIM / RTRIM: remove whitespace (important in ETL)
UPDATE users SET email = TRIM(email) WHERE email != TRIM(email);

-- LENGTH / CHAR_LENGTH: byte length vs character length (matters for multi-byte UTF-8)
SELECT id, CHAR_LENGTH(description) FROM products WHERE CHAR_LENGTH(description) > 500;

-- SUBSTRING: extract part of a string (1-based index)
SELECT SUBSTRING(phone, 1, 3) AS country_code FROM users; -- e.g. '+91'

-- REPLACE: substitute occurrences
SELECT REPLACE(description, 'old_brand', 'new_brand') FROM products;

-- POSITION / STRPOS: find first occurrence
SELECT POSITION('@' IN email)  AS at_pos FROM users;   -- standard
SELECT STRPOS(email, '@')      AS at_pos FROM users;   -- PostgreSQL alias

REGEXP and Anti-Patterns

Regular expressions handle complex patterns but bypass indexes. Using string functions on indexed columns in WHERE predicates forces full table scans — a frequent performance bug.

SQL — REGEXP, expression index, STRING_AGG
-- PostgreSQL REGEXP (~): validate Indian mobile numbers
SELECT id, phone FROM users WHERE phone ~ '^+91[6-9][0-9]{9}$';

-- MySQL REGEXP
SELECT id, phone FROM users WHERE phone REGEXP '^\+91[6-9][0-9]{9}$';

-- Anti-pattern: function on indexed column → sequential scan
-- Bad: index on email is NOT used
SELECT id FROM users WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';
-- Fix 1: expression index
CREATE INDEX idx_users_email_upper ON users(UPPER(email));
-- Fix 2: store email in lower-case on insert (application layer)
-- Fix 3: use ILIKE (PostgreSQL) — still no standard index, but pg_trgm helps

-- Anti-pattern: storing CSVs and splitting at query time
SELECT id, value FROM products
WHERE ',' || tags || ',' LIKE '%,electronics,%';  -- full scan, unmaintainable
-- Fix: normalise to a junction table (product_tags)

-- String aggregation: collapse many rows into one comma-separated value
SELECT user_id, STRING_AGG(product_name, ', ' ORDER BY product_name) AS purchased
FROM order_items oi JOIN products p ON p.id = oi.product_id
GROUP BY user_id;   -- PostgreSQL: STRING_AGG | MySQL: GROUP_CONCAT

Key Points to Remember

  • 1String functions on indexed columns in WHERE prevent index use — create expression indexes.
  • 2CONCAT_WS skips NULL values and inserts a separator — cleaner than nested CONCALTs.
  • 3CHAR_LENGTH returns character count; LENGTH returns byte count (different for UTF-8 multi-byte chars).
  • 4TRIM is essential in ETL pipelines where source data contains leading/trailing spaces.
  • 5STRING_AGG (PostgreSQL) / GROUP_CONCAT (MySQL) collapse rows into a delimited string.
  • 6Storing CSV in a column and parsing with string functions is an anti-pattern — normalise instead.

Interview Questions

Sign in to ask Aria
1

Why does using UPPER(email) in a WHERE clause prevent index usage?

MediumGoogle
2

What is the difference between LENGTH and CHAR_LENGTH in MySQL?

EasyAdobe
3

How would you aggregate multiple rows into a comma-separated list in PostgreSQL vs MySQL?

MediumSwiggy
4

What are the performance implications of using REGEXP in a WHERE clause?

MediumAmazon

Ask Aria about String Functions

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Sign in to save personal notes for this topic.

Discussion

Sign in to ask a question — Aria answers, and so do other students.

Loading discussion…