Chapter 21: String and Pattern Operations

Strings are among the most common data types in SQL. Operations include concatenation, trimming, replacing, substring extraction, and pattern matching using wildcards or regular expressions. Each engine follows the ANSI baseline but extends it with extra string and pattern tools.

Concatenation, SUBSTRING, TRIM, REPLACE

String construction and manipulation appear in most queries. Use ANSI forms when possible; switch to dialect-specific variants only for advanced use cases.

Concatenation

ANSI SQL defines || for concatenation, though some engines use + or CONCAT(). Prefer the function form for cross-engine portability.

-- ANSI
SELECT 'Hello' || ' ' || 'World' AS greeting;

-- MySQL / MariaDB
SELECT CONCAT('Hello', ' ', 'World') AS greeting;

-- SQL Server
SELECT 'Hello' + ' ' + 'World' AS greeting;

-- PostgreSQL supports both ANSI and CONCAT
SELECT CONCAT('Hello', ' ', 'World');

SUBSTRING extraction

SUBSTRING() (or SUBSTR()) retrieves part of a string. Position numbering starts at 1 in most engines. Negative indexes are engine-specific.

-- ANSI form
SELECT SUBSTRING('abcdef' FROM 2 FOR 3);  -- returns 'bcd'

-- MySQL / MariaDB
SELECT SUBSTRING('abcdef', 2, 3);

-- SQL Server
SELECT SUBSTRING('abcdef', 2, 3);

-- PostgreSQL (supports both forms)
SELECT SUBSTR('abcdef', 2, 3);

TRIM and whitespace cleanup

TRIM() removes leading, trailing, or both types of spaces (or custom characters). SQL also offers LTRIM() and RTRIM() in many dialects.

-- ANSI
SELECT TRIM('  text  ') AS cleaned;
SELECT TRIM(BOTH 'x' FROM 'xxxtrimxx') AS cleaned;

-- MySQL / MariaDB
SELECT TRIM('  text  '), LTRIM('  text  '), RTRIM('  text  ');

-- SQL Server
SELECT TRIM('  text  '), LTRIM('  text  '), RTRIM('  text  ');

-- PostgreSQL
SELECT TRIM('  text  ');

REPLACE and substitution

REPLACE() substitutes every occurrence of one substring with another. All major engines support it with identical signatures.

SELECT REPLACE('abc def abc', 'abc', 'XYZ');  -- returns 'XYZ def XYZ'

Other helpful functions

Regular expressions

Regular expressions extend pattern matching beyond LIKE wildcards. They follow POSIX, PCRE, or T-SQL style syntax depending on the database. Use them for validation, extraction, and conditional filtering, but be cautious with performance.

Matching and filtering

-- PostgreSQL (POSIX)
SELECT 'abc123' ~ '^[a-z]+[0-9]+$' AS is_match;

-- MySQL 8+ (default is case-insensitive unless specified)
SELECT 'abc123' REGEXP '^[a-z]+[0-9]+$';

-- SQL Server (use LIKE or CLR regex functions; no native REGEXP)
-- Example using a pattern for digits only
SELECT CASE WHEN name LIKE '%[0-9]%' THEN 1 ELSE 0 END AS has_digits FROM dbo.People;

Extraction and replacement

Some databases offer regex capture and substitution functions to transform text in one call.

-- PostgreSQL
SELECT REGEXP_REPLACE('abc123def', '[0-9]+', '###', 'g');  -- abc###def
SELECT REGEXP_MATCHES('id=42', '[0-9]+');                  -- returns 42

-- MySQL 8+
SELECT REGEXP_REPLACE('abc123def', '[0-9]+', '###');

Performance and indexes

Regex scans are CPU-intensive because they bypass most index optimizations. Restrict them with other predicates or use precomputed flags to reduce cost.

LIKE patterns

The LIKE operator supports basic wildcard matching using % (any number of characters) and _ (a single character). It works in all engines and often benefits from indexes when the pattern does not begin with a wildcard.

Basic usage

SELECT name FROM customers WHERE name LIKE 'A%';   -- begins with A
SELECT name FROM customers WHERE name LIKE '%son'; -- ends with son
SELECT name FROM customers WHERE name LIKE '_a%';  -- second letter is a

Case sensitivity

Default behavior varies. MySQL is case-insensitive for most collations. PostgreSQL is case-sensitive by default but provides ILIKE for case-insensitive matches. SQL Server depends on the database collation.

-- PostgreSQL
SELECT name FROM users WHERE name ILIKE 'john%';  -- case-insensitive

-- MySQL
SELECT name FROM users WHERE name LIKE 'john%';   -- case-insensitive in utf8_general_ci

-- SQL Server
SELECT name FROM users WHERE name LIKE 'john%';   -- depends on collation

Escaping special characters

To match literal % or _ symbols, define an escape character using ESCAPE.

SELECT filename
FROM logs
WHERE filename LIKE '%\%%' ESCAPE '\';  -- match a literal %

Combining with logic

Combine LIKE with logical operators for complex filters. Remember to group conditions with parentheses to maintain precedence.

SELECT title
FROM books
WHERE (title LIKE 'SQL%' OR title LIKE 'Database%')
  AND title NOT LIKE '%Advanced%';