SQL 50 🛢️ String Manipulation Functions
A string in SQL is a sequence of characters. In practice, it is rarely clean. It has leading and trailing whitespace, inconsistent casing, substrings that need extraction, parts that need concatenation, and lengths that need measuring. SQL provides a set of string functions for these operations, and although the exact names vary slightly between databases, the core functions are standardized and portable. This chapter covers the functions that appear most often in real queries: concatenation, length, case conversion, trimming, extraction, searching, and replacement.
Key point: String functions in SQL are mostly standard, but the names differ between databases. LENGTH in PostgreSQL is LEN in SQL Server. SUBSTRING is standard, but SQLite uses SUBSTR. CONCAT is standard, but the || operator is more portable for two arguments. Knowing the function and its variants across databases is part of writing portable SQL.
Why string functions matter
The data-cleaning problem. Data entered by humans is inconsistent. Names have extra spaces. Emails have mixed case. Phone numbers have different formats. Product codes have prefixes that need to be stripped. String functions are the first line of defense against dirty data. They normalize, trim, and standardize values before they are stored or compared.
The display problem. Reports need formatted output. A full name is the concatenation of first and last. A formatted address combines several fields with separators. A truncated description shows the first 100 characters. String functions produce the display values that users see.
The matching problem. Searching for a substring, finding the position of a character, or checking whether a string starts with a prefix are all string operations. LIKE and regular expressions handle pattern matching, but POSITION, INSTR, and SUBSTRING handle precise extraction.
The parsing problem. A single column may contain structured data that should be split. A CSV line has fields separated by commas. A log entry has a timestamp followed by a message. A URL has a domain and a path. String functions split these values into their components.
The database-difference problem. SQL string functions are not identical across databases. PostgreSQL, MySQL, and SQLite have their own names for common operations. Oracle uses SUBSTR and INSTR where PostgreSQL uses SUBSTRING and POSITION. SQL Server uses LEN where the standard uses LENGTH. The core functions are similar, but the details differ. This chapter uses the standard names and notes the common variants.
a. Concatenation, length, and case
Concatenation combines two or more strings. The standard operator is ||, supported by PostgreSQL, Oracle, SQLite, and MySQL (with PIPES_AS_CONCAT). SQL Server uses +. The CONCAT function is standard and accepts any number of arguments.
SELECT 'Hello' || ' ' || 'World'; -- 'Hello World'
SELECT CONCAT('Hello', ' ', 'World'); -- 'Hello World'The || operator is more portable for two arguments. CONCAT is more portable for many arguments and handles NULLs differently: CONCAT('a', NULL) returns 'a' in MySQL and PostgreSQL, while 'a' || NULL returns NULL.
Length returns the number of characters in a string. The standard function is LENGTH. SQL Server uses LEN, which excludes trailing spaces. MySQL and PostgreSQL use LENGTH, which counts bytes for CHAR columns in some configurations; CHAR_LENGTH is the character-counting function.
SELECT LENGTH('Hello'); -- 5
SELECT CHAR_LENGTH('Hello'); -- 5
SELECT LEN('Hello'); -- 5 (SQL Server)For multi-byte characters, LENGTH may return the byte count rather than the character count. CHAR_LENGTH or CHARACTER_LENGTH returns the character count. In PostgreSQL, LENGTH returns the character count for TEXT and VARCHAR.
Case conversion changes a string to uppercase or lowercase. The standard functions are UPPER and LOWER.
SELECT UPPER('Hello'); -- 'HELLO'
SELECT LOWER('Hello'); -- 'hello'These functions are useful for case-insensitive comparisons. WHERE LOWER(email) = LOWER(input) matches regardless of the case of either value. A functional index on LOWER(email) makes the comparison efficient.
MySQL also provides INITCAP, which capitalizes the first letter of each word. PostgreSQL provides INITCAP as well.
SELECT INITCAP('hello world'); -- 'Hello World'b. Trimming, extraction, and searching
Trimming removes characters from the beginning or end of a string. The standard functions are TRIM, LTRIM, and RTRIM.
SELECT TRIM(' Hello '); -- 'Hello'
SELECT LTRIM(' Hello '); -- 'Hello '
SELECT RTRIM(' Hello '); -- ' Hello'TRIM removes whitespace by default. It can also remove a specific character with the LEADING, TRAILING, or BOTH keywords:
SELECT TRIM(LEADING '0' FROM '000123'); -- '123'
SELECT TRIM(TRAILING '.' FROM 'Hello...'); -- 'Hello'Extraction returns a substring. The standard function is SUBSTRING, which takes a string, a start position, and optionally a length. The start position is 1-based, not 0-based.
SELECT SUBSTRING('Hello World', 1, 5); -- 'Hello'
SELECT SUBSTRING('Hello World', 7); -- 'World'
SELECT SUBSTRING('Hello World' FROM 7); -- 'World' (standard syntax)SQLite uses SUBSTR with the same semantics. SQL Server uses SUBSTRING with the same semantics. Oracle uses SUBSTR. PostgreSQL supports both SUBSTRING and SUBSTR.
The start position can be negative in some databases, counting from the end. In PostgreSQL, SUBSTRING('Hello' FROM -3) returns 'llo'. In MySQL, SUBSTRING('Hello', -3) returns 'llo'. This is not standard, and the behavior varies.
Searching finds the position of a substring. The standard function is POSITION, which returns the 1-based position of the first occurrence, or 0 if not found.
SELECT POSITION('World' IN 'Hello World'); -- 7
SELECT POSITION('xyz' IN 'Hello World'); -- 0SQL Server uses CHARINDEX. MySQL uses INSTR and LOCATE. Oracle uses INSTR. PostgreSQL uses both POSITION and STRPOS.
SELECT CHARINDEX('World', 'Hello World'); -- 7 (SQL Server)
SELECT INSTR('Hello World', 'World'); -- 7 (MySQL)
SELECT STRPOS('Hello World', 'World'); -- 7 (PostgreSQL)Replacement substitutes one substring for another. The standard function is REPLACE.
SELECT REPLACE('Hello World', 'World', 'SQL'); -- 'Hello SQL'
SELECT REPLACE('a-b-c', '-', '_'); -- 'a_b_c'REPLACE replaces all occurrences. To replace only the first occurrence, most databases require a different approach, such as combining POSITION and SUBSTRING.
Padding adds characters to the left or right of a string to reach a specified length. The standard functions are LPAD and RPAD.
SELECT LPAD('5', 3, '0'); -- '005'
SELECT RPAD('5', 3, '*'); -- '5**'These are useful for fixed-width output and for formatting identifiers.
c. Combining functions for real tasks
String functions are most useful when combined. A few patterns appear repeatedly.
Formatting a full name:
SELECT TRIM(CONCAT(first_name, ' ', last_name)) AS full_name
FROM persons;The CONCAT joins the parts with a space. The TRIM removes the leading or trailing space when one of the parts is empty or NULL.
Extracting a domain from an email:
SELECT SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM users;The POSITION finds the @, and the SUBSTRING extracts everything after it. This assumes the email is valid and contains exactly one @.
Normalizing an email for comparison:
SELECT LOWER(TRIM(email)) AS normalized_email
FROM users;The TRIM removes whitespace, and the LOWER converts to lowercase. The result can be compared or stored in a unique index.
Extracting initials:
SELECT
UPPER(SUBSTRING(first_name, 1, 1)) ||
UPPER(SUBSTRING(last_name, 1, 1)) AS initials
FROM persons;Each SUBSTRING extracts the first character, UPPER capitalizes it, and || concatenates them.
Truncating a description:
SELECT
CASE
WHEN LENGTH(description) > 100
THEN SUBSTRING(description, 1, 97) || '...'
ELSE description
END AS short_description
FROM products;The CASE checks the length, and the SUBSTRING takes the first 97 characters and appends an ellipsis.
Splitting a CSV line:
SELECT
SUBSTRING(line, 1, POSITION(',' IN line) - 1) AS first_field,
SUBSTRING(line, POSITION(',' IN line) + 1) AS rest
FROM csv_data;This splits at the first comma. Splitting at the second comma requires finding the position of the first comma, then searching for the next comma in the remaining substring. This gets tedious quickly, and databases with dedicated split functions—SPLIT_PART in PostgreSQL, STRING_SPLIT in SQL Server—are easier to use.
Removing a prefix:
SELECT SUBSTRING(code FROM LENGTH('PREFIX-') + 1) AS code_without_prefix
FROM products
WHERE code LIKE 'PREFIX-%';The SUBSTRING starts after the prefix. The WHERE clause ensures the substring is applied only to rows where the prefix is present.
Complete Example Session
-- ============================================
-- PART 1: CONCATENATION
-- ============================================
SELECT 'Hello' || ' ' || 'World'; -- 'Hello World'
SELECT CONCAT('Hello', ' ', 'World'); -- 'Hello World'
SELECT CONCAT('a', NULL); -- 'a' in PostgreSQL
SELECT 'a' || NULL; -- NULL-- ============================================
-- PART 2: LENGTH
-- ============================================
SELECT LENGTH('Hello'); -- 5
SELECT CHAR_LENGTH('Hello'); -- 5
SELECT LENGTH(''); -- 0
SELECT LENGTH(NULL); -- NULL-- ============================================
-- PART 3: CASE CONVERSION
-- ============================================
SELECT UPPER('Hello'); -- 'HELLO'
SELECT LOWER('Hello'); -- 'hello'
SELECT INITCAP('hello world'); -- 'Hello World'-- ============================================
-- PART 4: TRIMMING
-- ============================================
SELECT TRIM(' Hello '); -- 'Hello'
SELECT LTRIM(' Hello '); -- 'Hello '
SELECT RTRIM(' Hello '); -- ' Hello'
SELECT TRIM(LEADING '0' FROM '000123'); -- '123'
SELECT TRIM(TRAILING '.' FROM 'Hello...'); -- 'Hello'-- ============================================
-- PART 5: SUBSTRING
-- ============================================
SELECT SUBSTRING('Hello World', 1, 5); -- 'Hello'
SELECT SUBSTRING('Hello World', 7); -- 'World'
SELECT SUBSTRING('Hello World' FROM 7); -- 'World'
SELECT SUBSTR('Hello World', 1, 5); -- 'Hello' (SQLite)-- ============================================
-- PART 6: POSITION
-- ============================================
SELECT POSITION('World' IN 'Hello World'); -- 7
SELECT POSITION('xyz' IN 'Hello World'); -- 0
SELECT STRPOS('Hello World', 'World'); -- 7 (PostgreSQL)
SELECT INSTR('Hello World', 'World'); -- 7 (MySQL)-- ============================================
-- PART 7: REPLACE
-- ============================================
SELECT REPLACE('Hello World', 'World', 'SQL'); -- 'Hello SQL'
SELECT REPLACE('a-b-c', '-', '_'); -- 'a_b_c'
SELECT REPLACE('aaa', 'a', 'b'); -- 'bbb'-- ============================================
-- PART 8: PADDING
-- ============================================
SELECT LPAD('5', 3, '0'); -- '005'
SELECT RPAD('5', 3, '*'); -- '5**'
SELECT LPAD('hello', 10, ' '); -- ' hello'-- ============================================
-- PART 9: FULL NAME FORMATTING
-- ============================================
SELECT TRIM(CONCAT(first_name, ' ', last_name)) AS full_name
FROM persons;
-- Handles NULL first or last name gracefully-- ============================================
-- PART 10: EMAIL DOMAIN EXTRACTION
-- ============================================
SELECT
email,
SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM users;
-- 'alice@example.com' → 'example.com'The ten parts covered concatenation, length, case conversion, trimming, SUBSTRING, POSITION, REPLACE, padding, full name formatting, and email domain extraction.
Quick Reference
Core String Functions
| Function | Purpose | Standard Name |
|---|---|---|
| Concatenation | Join strings | CONCAT, || |
| Length | Character count | LENGTH, CHAR_LENGTH |
| Uppercase | Convert to upper | UPPER |
| Lowercase | Convert to lower | LOWER |
| Trim | Remove whitespace | TRIM, LTRIM, RTRIM |
| Substring | Extract part | SUBSTRING |
| Position | Find substring | POSITION |
| Replace | Substitute | REPLACE |
| Pad left | Add leading chars | LPAD |
| Pad right | Add trailing chars | RPAD |
Database Variants
| Operation | PostgreSQL | MySQL | SQL Server | SQLite |
|---|---|---|---|---|
| Length | LENGTH | LENGTH | LEN | LENGTH |
| Substring | SUBSTRING | SUBSTRING | SUBSTRING | SUBSTR |
| Position | POSITION, STRPOS | INSTR, LOCATE | CHARINDEX | INSTR |
| Concatenate | ||, CONCAT | CONCAT, || | +, CONCAT | || |
Common Patterns
| Pattern | Purpose |
|---|---|
TRIM(CONCAT(a, ' ', b)) | Format full name |
LOWER(TRIM(email)) | Normalize email |
SUBSTRING(email FROM POSITION('@' IN email) + 1) | Extract domain |
UPPER(SUBSTRING(a, 1, 1)) || UPPER(SUBSTRING(b, 1, 1)) | Initials |
LPAD(id, 5, '0') | Fixed-width ID |
REPLACE(code, '-', '') | Remove separators |
Trimming Options
| Function | Removes |
|---|---|
TRIM(s) | Leading and trailing whitespace |
LTRIM(s) | Leading whitespace |
RTRIM(s) | Trailing whitespace |
TRIM(LEADING c FROM s) | Leading occurrence of c |
TRIM(TRAILING c FROM s) | Trailing occurrence of c |
TRIM(BOTH c FROM s) | Both ends |
Best Practices
✅ Do This:
-- Use TRIM to clean input
SELECT TRIM(email) FROM users; -- ✅
-- Use LOWER for case-insensitive comparison
WHERE LOWER(email) = LOWER(:input) -- ✅
-- Use CONCAT for multiple arguments
SELECT CONCAT(first, ' ', last) FROM persons; -- ✅
-- Use SUBSTRING with FROM for standard syntax
SELECT SUBSTRING(email FROM POSITION('@' IN email) + 1); -- ✅
-- Use LPAD for fixed-width output
SELECT LPAD(id, 5, '0'); -- ✅
-- Use CASE before SUBSTRING to handle short strings
CASE WHEN LENGTH(s) > 10 THEN SUBSTRING(s, 1, 10) END -- ✅❌ Don’t Do This:
-- Don't assume || handles NULL like CONCAT
SELECT 'a' || NULL; -- NULL -- ⚠️
-- Don't use LENGTH when CHAR_LENGTH is needed
SELECT LENGTH('café'); -- byte count in some DBs -- ⚠️
-- Don't forget that SUBSTRING is 1-based
SELECT SUBSTRING('Hello', 0, 1); -- '' in some DBs -- ⚠️
-- Don't replace only some occurrences with REPLACE
SELECT REPLACE('a-b-c', '-', '_'); -- replaces all -- ✅
-- To replace one, use POSITION + SUBSTRING
-- Don't apply SUBSTRING without checking length
SELECT SUBSTRING(s, 1, 100); -- safe, returns whole string -- ✅
-- SUBSTRING with start beyond length returns empty string
-- Don't mix function names across databases
SELECT LEN(s); -- SQL Server only -- ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
|| returns NULL | NULL operand | Use CONCAT |
LENGTH returns bytes | Multi-byte characters | Use CHAR_LENGTH |
SUBSTRING returns empty | Start position beyond length | Check length first |
POSITION returns 0 | Substring not found | Check before using in SUBSTRING |
| Wrong function name | Database difference | Use the correct variant |
REPLACE too broad | Replaces all occurrences | Use POSITION + SUBSTRING |
| Trailing spaces ignored | LEN in SQL Server | Use DATALENGTH for bytes |
Real-World Examples
1. Full Name
SELECT TRIM(CONCAT(first_name, ' ', last_name)) AS full_name
FROM persons;2. Normalize Email
SELECT LOWER(TRIM(email)) AS email FROM users;3. Extract Domain
SELECT SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM users;4. Initials
SELECT
UPPER(SUBSTRING(first_name, 1, 1)) ||
UPPER(SUBSTRING(last_name, 1, 1)) AS initials
FROM persons;5. Truncate Description
SELECT
CASE WHEN LENGTH(description) > 100
THEN SUBSTRING(description, 1, 97) || '...'
ELSE description
END AS short_description
FROM products;6. Fixed-Width ID
SELECT LPAD(customer_id::text, 6, '0') AS padded_id
FROM customers;7. Remove Prefix
SELECT SUBSTRING(code FROM LENGTH('SKU-') + 1) AS sku
FROM products
WHERE code LIKE 'SKU-%';8. Replace Separators
SELECT REPLACE(phone, '-', '') AS digits_only FROM contacts;9. Check Prefix
SELECT * FROM products
WHERE LEFT(code, 4) = 'SKU-';10. Capitalize First Letter
SELECT UPPER(SUBSTRING(name, 1, 1)) || LOWER(SUBSTRING(name, 2)) AS proper_name
FROM users;Visual
String Function Flow
┌─────────────────────────────────────────────────────────────┐
│ TYPICAL DATA CLEANING PIPELINE │
│ │
│ Raw input: ' ALICE@EXAMPLE.COM ' │
│ │ │
│ ▼ │
│ TRIM → 'ALICE@EXAMPLE.COM' │
│ │ │
│ ▼ │
│ LOWER → 'alice@example.com' │
│ │ │
│ ▼ │
│ SUBSTRING + POSITION → 'example.com' │
│ │
│ Each function transforms the value. │
│ They compose left to right. │
│ │
└─────────────────────────────────────────────────────────────┘SUBSTRING and POSITION Together
┌─────────────────────────────────────────────────────────────┐
│ EXTRACT DOMAIN FROM EMAIL │
│ │
│ email = 'alice@example.com' │
│ │
│ POSITION('@' IN email) → 6 │
│ │
│ SUBSTRING(email FROM 6 + 1) → SUBSTRING(email FROM 7) │
│ │
│ 'alice@example.com' │
│ 1234567890123456 │
│ ↑ │
│ position 6 = '@' │
│ start at 7 = 'e' │
│ │
│ Result: 'example.com' │
│ │
└─────────────────────────────────────────────────────────────┘Trimming Variants
┌─────────────────────────────────────────────────────────────┐
│ INPUT: ' Hello ' │
│ │
│ TRIM → 'Hello' │
│ LTRIM → 'Hello ' │
│ RTRIM → ' Hello' │
│ │
│ INPUT: '000123' │
│ │
│ TRIM(LEADING '0' FROM '000123') → '123' │
│ │
│ INPUT: 'Hello...' │
│ │
│ TRIM(TRAILING '.' FROM 'Hello...') → 'Hello' │
│ │
└─────────────────────────────────────────────────────────────┘Database Function Names
┌─────────────────────────────────────────────────────────────┐
│ LENGTH │
│ PostgreSQL: LENGTH, CHAR_LENGTH │
│ MySQL: LENGTH, CHAR_LENGTH │
│ SQL Server: LEN │
│ SQLite: LENGTH │
│ │
│ SUBSTRING │
│ PostgreSQL: SUBSTRING, SUBSTR │
│ MySQL: SUBSTRING, SUBSTR │
│ SQL Server: SUBSTRING │
│ SQLite: SUBSTR │
│ │
│ POSITION │
│ PostgreSQL: POSITION, STRPOS │
│ MySQL: INSTR, LOCATE │
│ SQL Server: CHARINDEX │
│ SQLite: INSTR │
│ │
│ CONCATENATION │
│ PostgreSQL: ||, CONCAT │
│ MySQL: CONCAT, || │
│ SQL Server: +, CONCAT │
│ SQLite: || │
│ │
└─────────────────────────────────────────────────────────────┘Summary
| Function | Purpose | Notes |
|---|---|---|
CONCAT | Join strings | Handles NULL as empty |
|| | Join strings | NULL propagates |
LENGTH | Character count | Byte count in some DBs |
CHAR_LENGTH | Character count | Always characters |
UPPER | Uppercase | Standard |
LOWER | Lowercase | Standard |
INITCAP | Title case | PostgreSQL, MySQL |
TRIM | Remove whitespace | Both ends |
LTRIM | Remove leading | Left side |
RTRIM | Remove trailing | Right side |
SUBSTRING | Extract part | 1-based index |
POSITION | Find substring | Returns 0 if not found |
REPLACE | Substitute | All occurrences |
LPAD | Pad left | Fixed width |
RPAD | Pad right | Fixed width |
Key takeaways:
- String functions are mostly standard but the names vary.
LENGTHisLENin SQL Server.SUBSTRINGisSUBSTRin SQLite and Oracle.POSITIONisCHARINDEXin SQL Server andINSTRin MySQL. Knowing the variants is necessary for portable SQL. - Concatenation has two forms. The
||operator is standard and portable for two arguments.CONCATis a function that accepts multiple arguments and handles NULLs differently:CONCAT('a', NULL)returns'a'in most databases, while'a' || NULLreturns NULL. LENGTHmay return bytes, not characters. For multi-byte character sets,LENGTHcan return the byte count.CHAR_LENGTHorCHARACTER_LENGTHalways returns the character count.TRIMhas several forms.TRIMremoves whitespace from both ends.LTRIMandRTRIMremove from one end.TRIM(LEADING c FROM s)andTRIM(TRAILING c FROM s)remove specific characters.SUBSTRINGis 1-based. The first character is at position 1. A start position of 0 is invalid in some databases and produces an empty string in others. A start position beyond the string length produces an empty string.POSITIONreturns 0 when the substring is not found. This is different from returning NULL. The return value can be used directly inSUBSTRING, but if the substring is not found, the result is unpredictable. Check the position first when the substring may be absent.REPLACEreplaces all occurrences. To replace only the first occurrence, combinePOSITIONandSUBSTRING. This is more verbose but necessary when the replacement should be limited.- String functions compose.
TRIM(CONCAT(first, ' ', last))formats a name.SUBSTRING(email FROM POSITION('@' IN email) + 1)extracts a domain.LOWER(TRIM(email))normalizes an email. The functions are building blocks, and the patterns are built from combinations.
Remember: Strings are the most common data type in real databases, and they are rarely clean. String functions are the tools for normalizing, extracting, formatting, and validating them. The core functions—concatenation, length, case conversion, trimming, substring, position, replace, and padding—cover most of what is needed. The names differ between databases, but the semantics are similar. Learn the standard names, know the common variants, and combine the functions to handle the real tasks: formatting names, extracting domains, normalizing emails, truncating descriptions, and cleaning input. String manipulation is not glamorous, but it is unavoidable, and the functions that do it are the ones used most often.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!