SQL Built-In String Functions | Tutorial Reference

Master essential SQL string functions like CONCAT, UPPER, LOWER, TRIM, SUBSTRING, and REPLACE with practical examples and database compatibility notes for text manipulation in queries.

Text data is everywhere in databases. Customer names, email addresses, product descriptions, status codes, addresses, URLs, log messages. Rarely does this text arrive in the exact format you need. Names come in mixed case, fields have trailing spaces, you need to extract domains from email addresses, or you must combine first and last names into a single display name.

SQL string functions are the tools that let you manipulate, transform, and extract text data directly in your queries without pulling it into application code. Mastering them means cleaner queries, less data processing in your application layer, and the ability to fix messy data right where it lives.

This guide covers every essential string function with practical examples, shows the output of each operation, and highlights the syntax differences across databases so you can write portable, effective text-manipulation queries.

The Sample Data

All examples use this table:

CREATE TABLE contacts (
    contact_id  INT PRIMARY KEY,
    first_name  VARCHAR(50),
    last_name   VARCHAR(50),
    email       VARCHAR(100),
    phone       VARCHAR(20),
    city        VARCHAR(50),
    notes       VARCHAR(500)
);

INSERT INTO contacts VALUES
(1, 'Alice',   'Martin',    'alice.martin@example.com',   '555-0101', '  New York  ', 'VIP customer since 2019'),
(2, 'bob',     'JONES',     'BOB.JONES@CORP.COM',         '555-0102', 'Chicago',      'Prefers email contact'),
(3, 'Carol',   'Smith',     'carol_smith@gmail.com',      '555-0103', 'denver',       'Referred by Alice Martin'),
(4, 'Dave',    'O''Brien',  'dave.obrien@example.com',    NULL,       'San Francisco','New customer - needs follow-up'),
(5, 'Eve',     'Turner',    'eve@oldcompany.com',         '555-0105', 'BOSTON',       NULL);

Notice the intentionally messy data: mixed case, leading/trailing spaces in city names, inconsistent email casing, and NULLs.

CONCAT: Combining Strings

CONCAT joins two or more strings into one. It is the most frequently used string function.

Syntax

-- Standard SQL / MySQL / PostgreSQL / SQL Server
CONCAT(string1, string2, ...)

-- PostgreSQL / Oracle: || operator
string1 || string2

-- MySQL: also supports ||, but only with PIPES_AS_CONCAT mode

Examples

Combine first and last name:

SELECT
    contact_id,
    CONCAT(first_name, ' ', last_name) AS full_name
FROM contacts;

contact_id	full_name
1	Alice Martin
2	bob JONES
3	Carol Smith
4	Dave O'Brien
5	Eve Turner

Build a formatted string:

SELECT
    CONCAT(first_name, ' ', last_name, ' <', email, '>') AS contact_line
FROM contacts
WHERE contact_id = 1;

contact_line
Alice Martin <alice.martin@example.com>

PostgreSQL / Oracle with || operator:

SELECT first_name || ' ' || last_name AS full_name
FROM contacts;

CONCAT and NULL Handling

This is a critical difference between CONCAT() and the || operator:

-- CONCAT() treats NULL as empty string (MySQL, SQL Server, PostgreSQL)
SELECT CONCAT(first_name, ' - Phone: ', phone) AS info
FROM contacts WHERE contact_id = 4;
-- Result: 'Dave - Phone: '  (NULL becomes empty)

-- || operator: NULL makes the entire result NULL (PostgreSQL, Oracle)
SELECT first_name || ' - Phone: ' || phone AS info
FROM contacts WHERE contact_id = 4;
-- Result: NULL

Warning

In PostgreSQL and Oracle, the || operator returns NULL if any operand is NULL. Use CONCAT() or wrap values with COALESCE() to handle NULLs safely:

SELECT first_name || ' - Phone: ' || COALESCE(phone, 'N/A') AS info
FROM contacts WHERE contact_id = 4;
-- Result: 'Dave - Phone: N/A'

CONCAT_WS: Concatenate With Separator

CONCAT_WS joins strings with a specified separator, automatically skipping NULLs. Available in MySQL, PostgreSQL, SQL Server (2017+), and MariaDB.

SELECT CONCAT_WS(', ', first_name, last_name, city) AS summary
FROM contacts;

summary
Alice, Martin, New York
bob, JONES, Chicago
Carol, Smith, denver
Dave, O'Brien, San Francisco
Eve, Turner, BOSTON

-- NULLs are skipped, not turned into empty strings
SELECT CONCAT_WS(' | ', first_name, phone, notes) AS info
FROM contacts WHERE contact_id = 4;
-- Result: 'Dave | New customer - needs follow-up'
-- phone (NULL) was silently skipped

LENGTH: Measuring String Length

LENGTH returns the number of characters in a string.

Syntax Variations

-- PostgreSQL / MySQL / SQLite
LENGTH(string)

-- SQL Server
LEN(string)         -- trims trailing spaces
DATALENGTH(string)  -- counts bytes, not characters

-- Oracle
LENGTH(string)

Examples

SELECT
    first_name,
    email,
    LENGTH(first_name) AS name_len,
    LENGTH(email) AS email_len
FROM contacts;

first_name	email	name_len	email_len
Alice	alice.martin@example.com	5	24
bob	BOB.JONES@CORP.COM	3	18
Carol	carol_smith@gmail.com	5	21
Dave	dave.obrien@example.com	4	23
Eve	eve@oldcompany.com	3	18

Finding unusually long or short values:

-- Emails longer than 22 characters
SELECT email, LENGTH(email) AS len
FROM contacts
WHERE LENGTH(email) > 22;

email	len
alice.martin@example.com	24
dave.obrien@example.com	23

Info

SQL Server's LEN() automatically trims trailing spaces before counting. If you need to count trailing spaces, use DATALENGTH() and divide by the bytes-per-character for your encoding.

-- SQL Server
SELECT LEN('hello   ');        -- Returns 5 (trailing spaces trimmed)
SELECT DATALENGTH('hello   '); -- Returns 8 (counts all bytes)

UPPER and LOWER: Changing Case

UPPER converts all characters to uppercase. LOWER converts all characters to lowercase.

Syntax

UPPER(string)
LOWER(string)

These functions work identically across all major databases.

Examples

SELECT
    first_name,
    last_name,
    UPPER(first_name) AS upper_name,
    LOWER(email) AS lower_email
FROM contacts;

first_name	last_name	upper_name	lower_email
Alice	Martin	ALICE	alice.martin@example.com
bob	JONES	BOB	bob.jones@corp.com
Carol	Smith	CAROL	carol_smith@gmail.com
Dave	O'Brien	DAVE	dave.obrien@example.com
Eve	Turner	EVE	eve@oldcompany.com

Practical use: case-insensitive comparisons:

-- Without normalization: misses 'bob' because it doesn't match 'Bob'
SELECT * FROM contacts WHERE first_name = 'Bob';
-- Returns 0 rows!

-- With UPPER/LOWER: catches all case variations
SELECT * FROM contacts WHERE LOWER(first_name) = 'bob';
-- Returns bob JONES

Normalizing data during insertion or updates:

-- Store emails consistently in lowercase
UPDATE contacts SET email = LOWER(email);

-- Capitalize city names properly
UPDATE contacts SET city = UPPER(city);

Tip

For case-insensitive searches in PostgreSQL, use the ILIKE operator instead of converting with LOWER():

-- PostgreSQL: ILIKE is case-insensitive LIKE
SELECT * FROM contacts WHERE first_name ILIKE 'bob';

-- MySQL: LIKE is case-insensitive by default (depends on collation)
SELECT * FROM contacts WHERE first_name LIKE 'bob';

INITCAP: Title Case (PostgreSQL / Oracle)

INITCAP capitalizes the first letter of each word:

-- PostgreSQL / Oracle only
SELECT INITCAP('hello world') AS title_case;
-- Result: 'Hello World'

-- PostgreSQL / Oracle only
SELECT INITCAP(LOWER(city)) AS city_proper
FROM contacts;

city_proper
New York
Chicago
Denver
San Francisco
Boston

MySQL and SQL Server do not have INITCAP. You would need a custom function or application-level formatting.

TRIM, LTRIM, RTRIM: Removing Whitespace

These functions remove unwanted spaces (or other characters) from the beginning, end, or both sides of a string.

Syntax

-- Remove spaces from both sides
TRIM(string)

-- Remove from left side only
LTRIM(string)

-- Remove from right side only
RTRIM(string)

-- Remove specific characters (PostgreSQL / Oracle / MySQL 8.0+)
TRIM(LEADING '0' FROM string)
TRIM(TRAILING '.' FROM string)
TRIM(BOTH 'x' FROM string)

Examples

Recall that contact 1 has ' New York ' as the city (leading and trailing spaces):

SELECT
    city,
    LENGTH(city) AS original_len,
    TRIM(city) AS trimmed,
    LENGTH(TRIM(city)) AS trimmed_len
FROM contacts
WHERE contact_id = 1;

city	original_len	trimmed	trimmed_len
" New York "	12	"New York"	8

The spaces are gone, and the length dropped from 12 to 8.

LTRIM and RTRIM separately:

SELECT
    LTRIM('   hello   ') AS left_trimmed,
    RTRIM('   hello   ') AS right_trimmed,
    TRIM('   hello   ')  AS both_trimmed;

left_trimmed	right_trimmed	both_trimmed
"hello "	" hello"	"hello"

Trimming specific characters:

-- Remove leading zeros from a product code
SELECT TRIM(LEADING '0' FROM '000425') AS code;
-- Result: '425'

-- Remove trailing dots
SELECT TRIM(TRAILING '.' FROM 'hello...') AS cleaned;
-- Result: 'hello'

-- Remove surrounding dashes
SELECT TRIM(BOTH '-' FROM '--hello--') AS cleaned;
-- Result: 'hello'

Practical use: cleaning data before comparisons:

-- Without TRIM, this comparison fails due to hidden spaces
SELECT * FROM contacts WHERE city = 'New York';
-- May return 0 rows if city has leading/trailing spaces!

-- Safe comparison
SELECT * FROM contacts WHERE TRIM(city) = 'New York';

Tip

It is better to clean data on insert rather than trimming in every query. Use a trigger or application-level validation to trim values before they enter the database:

-- Clean up existing data
UPDATE contacts SET city = TRIM(city);

SUBSTRING: Extracting Parts of a String

SUBSTRING (or SUBSTR) extracts a portion of a string based on a starting position and optional length.

Syntax

-- Standard SQL / PostgreSQL / MySQL
SUBSTRING(string FROM start FOR length)
SUBSTRING(string, start, length)

-- SQL Server
SUBSTRING(string, start, length)

-- Oracle / SQLite
SUBSTR(string, start, length)

Examples

Extract the first 3 characters:

SELECT
    first_name,
    SUBSTRING(first_name, 1, 3) AS abbreviation
FROM contacts;

first_name	abbreviation
Alice	Ali
bob	bob
Carol	Car
Dave	Dav
Eve	Eve

Extract email domain:

SELECT
    email,
    SUBSTRING(email, POSITION('@' IN email) + 1) AS domain
FROM contacts;

email	domain
alice.martin@example.com	example.com
BOB.JONES@CORP.COM	CORP.COM
carol_smith@gmail.com	gmail.com
dave.obrien@example.com	example.com
eve@oldcompany.com	oldcompany.com

When you omit the length, SUBSTRING returns everything from the start position to the end of the string.

Extract email username:

SELECT
    email,
    SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS username
FROM contacts;

email	username
alice.martin@example.com	alice.martin
BOB.JONES@CORP.COM	BOB.JONES
carol_smith@gmail.com	carol_smith
dave.obrien@example.com	dave.obrien
eve@oldcompany.com	eve

Extract area code from phone:

SELECT
    phone,
    SUBSTRING(phone, 1, 3) AS area_code
FROM contacts
WHERE phone IS NOT NULL;

phone	area_code
555-0101	555
555-0102	555
555-0103	555
555-0105	555

LEFT and RIGHT: Shortcut Extraction

LEFT returns the first N characters from the left. RIGHT returns the last N characters from the right. They are convenience wrappers around SUBSTRING.

Syntax

LEFT(string, number_of_characters)
RIGHT(string, number_of_characters)

Available in MySQL, PostgreSQL, SQL Server, Oracle. SQLite does not have LEFT/RIGHT; use SUBSTR instead.

Examples

SELECT
    email,
    LEFT(email, 5) AS first_five,
    RIGHT(email, 4) AS last_four
FROM contacts;

email	first_five	last_four
alice.martin@example.com	alice	.com
BOB.JONES@CORP.COM	BOB.J	.COM
carol_smith@gmail.com	carol	.com
dave.obrien@example.com	dave.	.com
eve@oldcompany.com	eve@o	.com

Practical use: masking sensitive data:

SELECT
    email,
    LEFT(email, 2) || '***' || SUBSTRING(email, POSITION('@' IN email)) AS masked_email
FROM contacts;

email	masked_email
alice.martin@example.com	al***@example.com
BOB.JONES@CORP.COM	BO***@CORP.COM
carol_smith@gmail.com	ca***@gmail.com

Extracting file extensions:

SELECT RIGHT('report_2024.pdf', 3) AS extension;
-- Result: 'pdf'

Tip

Oracle alternative for LEFT and RIGHT:

-- Oracle: LEFT equivalent
SELECT SUBSTR(email, 1, 5) FROM contacts;

-- Oracle: RIGHT equivalent
SELECT SUBSTR(email, -4) FROM contacts;  -- negative position counts from end

REPLACE: Substituting Text

REPLACE swaps all occurrences of a substring with a different string.

Syntax

REPLACE(string, search_string, replacement_string)

Works identically across all major databases.

Examples

Replace characters in a string:

-- Remove dashes from phone numbers
SELECT
    phone,
    REPLACE(phone, '-', '') AS clean_phone
FROM contacts
WHERE phone IS NOT NULL;

phone	clean_phone
555-0101	5550101
555-0102	5550102
555-0103	5550103
555-0105	5550105

Update email domains:

SELECT
    email,
    REPLACE(email, 'oldcompany.com', 'newcompany.com') AS updated_email
FROM contacts;

email	updated_email
alice.martin@example.com	alice.martin@example.com
BOB.JONES@CORP.COM	BOB.JONES@CORP.COM
carol_smith@gmail.com	carol_smith@gmail.com
dave.obrien@example.com	dave.obrien@example.com
eve@oldcompany.com	eve@newcompany.com

Only Eve's email changed because hers was the only one containing 'oldcompany.com'.

Chaining REPLACE for multiple substitutions:

-- Sanitize user input: remove multiple problematic characters
SELECT REPLACE(REPLACE(REPLACE('Hello <World> & "Friends"',
    '<', ''),
    '>', ''),
    '"', '')
AS sanitized;
-- Result: 'Hello World & Friends'

Remove all spaces from a string:

SELECT REPLACE('  New   York  ', ' ', '') AS no_spaces;
-- Result: 'NewYork'

POSITION: Finding Substrings

POSITION returns the starting position of a substring within a string. If the substring is not found, it returns 0.

Syntax Variations

-- Standard SQL / PostgreSQL
POSITION(substring IN string)

-- MySQL / PostgreSQL also support
LOCATE(substring, string)

-- SQL Server
CHARINDEX(substring, string)

-- Oracle
INSTR(string, substring)

Examples

SELECT
    email,
    POSITION('@' IN email) AS at_position
FROM contacts;

email	at_position
alice.martin@example.com	13
BOB.JONES@CORP.COM	10
carol_smith@gmail.com	12
dave.obrien@example.com	12
eve@oldcompany.com	4

Check if a substring exists:

-- Find contacts with Gmail addresses
SELECT first_name, email
FROM contacts
WHERE POSITION('gmail' IN LOWER(email)) > 0;

first_name	email
Carol	carol_smith@gmail.com

Combining POSITION with SUBSTRING:

-- Extract everything before the first dot in an email
SELECT
    email,
    SUBSTRING(email, 1, POSITION('.' IN email) - 1) AS first_part
FROM contacts
WHERE POSITION('.' IN email) > 0;

email	first_part
alice.martin@example.com	alice
BOB.JONES@CORP.COM	BOB
dave.obrien@example.com	dave

Info

POSITION / LOCATE / CHARINDEX returns the position of the first occurrence. To find subsequent occurrences, some databases offer additional parameters:

-- MySQL: LOCATE with start position (find second occurrence)
SELECT LOCATE('.', 'alice.martin@example.com', 6) AS second_dot;
-- Starts searching from position 6, finds the dot at position 21

-- Oracle: INSTR with occurrence parameter
SELECT INSTR('alice.martin@example.com', '.', 1, 2) AS second_dot;
-- Finds the 2nd occurrence of '.' starting from position 1

Combining Functions: Practical Recipes

Real-world text manipulation usually requires combining multiple functions. Here are common patterns.

Proper Case for Names

-- Normalize messy name data to consistent format
SELECT
    contact_id,
    CONCAT(UPPER(LEFT(first_name, 1)), LOWER(SUBSTRING(first_name, 2))) AS first_name,
    CONCAT(UPPER(LEFT(last_name, 1)), LOWER(SUBSTRING(last_name, 2))) AS last_name
FROM contacts;

contact_id	first_name	last_name
1	Alice	Martin
2	Bob	Jones
3	Carol	Smith
4	Dave	O'brien
5	Eve	Turner

Info

Notice that "O'Brien" became "O'brien" because our logic only capitalizes the first letter. Handling apostrophes and hyphenated names correctly requires more complex logic or PostgreSQL's INITCAP():

-- PostgreSQL: INITCAP handles this correctly
SELECT INITCAP('o''brien');  -- Result: 'O'Brien'
SELECT INITCAP('mary-jane'); -- Result: 'Mary-Jane'

Extract Domain and Classify Email Providers

SELECT
    email,
    LOWER(SUBSTRING(email, POSITION('@' IN email) + 1)) AS domain,
    CASE
        WHEN LOWER(email) LIKE '%@gmail.com' THEN 'Gmail'
        WHEN LOWER(email) LIKE '%@corp.com'  THEN 'Corporate'
        WHEN LOWER(email) LIKE '%@example.com' THEN 'Example'
        ELSE 'Other'
    END AS provider_type
FROM contacts;

email	domain	provider_type
alice.martin@example.com	example.com	Example
BOB.JONES@CORP.COM	corp.com	Corporate
carol_smith@gmail.com	gmail.com	Gmail
dave.obrien@example.com	example.com	Example
eve@oldcompany.com	oldcompany.com	Other

Generate a Username from Full Name

SELECT
    first_name,
    last_name,
    LOWER(
        CONCAT(
            LEFT(first_name, 1),
            last_name
        )
    ) AS username
FROM contacts;

first_name	last_name	username
Alice	Martin	amartin
bob	JONES	bjones
Carol	Smith	csmith
Dave	O'Brien	do'brien
Eve	Turner	eturner

For Dave, the apostrophe in O'Brien creates a problematic username. Add REPLACE to handle it:

SELECT
    LOWER(REPLACE(CONCAT(LEFT(first_name, 1), last_name), '''', '')) AS username
FROM contacts;
-- Dave's result: 'dobrien'

Clean and Standardize Phone Numbers

SELECT
    phone,
    CONCAT(
        '(',
        LEFT(REPLACE(phone, '-', ''), 3),
        ') ',
        SUBSTRING(REPLACE(phone, '-', ''), 4, 3),
        '-',
        RIGHT(REPLACE(phone, '-', ''), 4)
    ) AS formatted_phone
FROM contacts
WHERE phone IS NOT NULL;

phone	formatted_phone
555-0101	(555) 010-0101

Search Within Notes

-- Find contacts whose notes mention "Alice"
SELECT
    contact_id,
    first_name,
    notes,
    POSITION('Alice' IN notes) AS mention_pos
FROM contacts
WHERE notes IS NOT NULL
  AND POSITION('Alice' IN notes) > 0;

contact_id	first_name	notes	mention_pos
3	Carol	Referred by Alice Martin	13

Database Compatibility Reference

Function	PostgreSQL	MySQL	SQL Server	Oracle	SQLite
`CONCAT()`	Yes	Yes	Yes	Yes (12c+)	No (use `\|\|`)
`\|\|` operator	Yes	With mode	No (use `+`)	Yes	Yes
`CONCAT_WS()`	Yes	Yes	Yes (2017+)	No	No
`LENGTH()`	Yes	Yes	Use `LEN()`	Yes	Yes
`UPPER()` / `LOWER()`	Yes	Yes	Yes	Yes	Yes
`TRIM()`	Yes	Yes	Use `LTRIM`/`RTRIM`	Yes	Yes
`SUBSTRING()`	Yes	Yes	Yes	Use `SUBSTR()`	Use `SUBSTR()`
`LEFT()` / `RIGHT()`	Yes	Yes	Yes	Use `SUBSTR()`	Yes
`REPLACE()`	Yes	Yes	Yes	Yes	Yes
`POSITION()`	Yes	Use `LOCATE()`	Use `CHARINDEX()`	Use `INSTR()`	Use `INSTR()`
`INITCAP()`	Yes	No	No	Yes	No
`LPAD()` / `RPAD()`	Yes	Yes	No	Yes	No
`REVERSE()`	Yes	Yes	Yes	No	No

Tip

SQL Server uses + for string concatenation instead of ||:

-- SQL Server
SELECT first_name + ' ' + last_name AS full_name FROM contacts;

-- Be careful: + with NULL returns NULL in SQL Server
SELECT 'Hello' + NULL;  -- Result: NULL

-- Use CONCAT() for NULL-safe concatenation
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM contacts;

Bonus Functions Worth Knowing

LPAD and RPAD: Padding Strings

Pad a string to a specific length with a fill character:

-- PostgreSQL / MySQL / Oracle
SELECT
    LPAD('42', 6, '0') AS padded_left,
    RPAD('Hi', 10, '.') AS padded_right;

padded_left	padded_right
000042	Hi........

Useful for generating fixed-width codes, invoice numbers, or formatted output.

REVERSE: Reversing a String

-- PostgreSQL / MySQL / SQL Server
SELECT REVERSE('Hello') AS reversed;
-- Result: 'olleH'

REPEAT: Repeating a String

-- PostgreSQL / MySQL
SELECT REPEAT('*', 10) AS stars;
-- Result: '**********'

-- SQL Server
SELECT REPLICATE('*', 10) AS stars;

Common Mistakes

Mistake 1: Forgetting Case Sensitivity in Comparisons

-- This misses 'bob' because WHERE is case-sensitive in many databases
SELECT * FROM contacts WHERE first_name = 'Bob';
-- Returns 0 rows in PostgreSQL!

Fix:

SELECT * FROM contacts WHERE LOWER(first_name) = 'bob';
-- Or in PostgreSQL: WHERE first_name ILIKE 'bob'

Mistake 2: Using String Functions on NULL

Most string functions return NULL when given a NULL input:

SELECT UPPER(NULL);        -- Result: NULL
SELECT LENGTH(NULL);       -- Result: NULL
SELECT CONCAT('Hi', NULL); -- Result depends on database!

Fix: Use COALESCE to provide fallback values:

SELECT UPPER(COALESCE(notes, 'No notes')) AS notes_upper FROM contacts;

Mistake 3: Assuming 0-Based String Indexing

SQL strings are 1-based, not 0-based:

-- Wrong: position 0 does not exist (or returns empty in some databases)
SELECT SUBSTRING('Hello', 0, 3);
-- May return 'He' or '' or error depending on database

-- Correct: start at position 1
SELECT SUBSTRING('Hello', 1, 3);
-- Result: 'Hel'

Mistake 4: Using String Functions on Indexed Columns in WHERE

Applying a function to a column in a WHERE clause prevents the database from using an index on that column:

-- Slow: index on email cannot be used because of LOWER()
SELECT * FROM contacts WHERE LOWER(email) = 'bob.jones@corp.com';

-- Better: create a functional index
CREATE INDEX idx_contacts_email_lower ON contacts (LOWER(email));
-- Now LOWER(email) comparisons use the index

Quick Reference

Task	Function	Example
Combine strings	`CONCAT()` or `\|\|`	`CONCAT(first, ' ', last)`
String length	`LENGTH()` / `LEN()`	`LENGTH('Hello')` → 5
Uppercase	`UPPER()`	`UPPER('hello')` → `'HELLO'`
Lowercase	`LOWER()`	`LOWER('HELLO')` → `'hello'`
Remove spaces	`TRIM()`	`TRIM(' hi ')` → `'hi'`
Left trim	`LTRIM()`	`LTRIM(' hi')` → `'hi'`
Right trim	`RTRIM()`	`RTRIM('hi ')` → `'hi'`
Extract substring	`SUBSTRING()`	`SUBSTRING('Hello', 1, 3)` → `'Hel'`
First N chars	`LEFT()`	`LEFT('Hello', 3)` → `'Hel'`
Last N chars	`RIGHT()`	`RIGHT('Hello', 3)` → `'llo'`
Find position	`POSITION()`	`POSITION('@' IN email)`
Replace text	`REPLACE()`	`REPLACE('a-b', '-', '_')` → `'a_b'`

Summary

SQL string functions give you the power to manipulate text data directly in your queries, from simple case conversion to complex parsing and transformation. They are essential tools for data cleaning, formatting output, building search features, and preparing reports.

Key takeaways:

CONCAT joins strings together. Use CONCAT() over || for NULL-safe concatenation. Use CONCAT_WS when you need a consistent separator.
LENGTH measures string length. Remember that SQL Server uses LEN() instead.
UPPER and LOWER normalize case for display and comparison. Use them (or ILIKE in PostgreSQL) for case-insensitive searches.
TRIM, LTRIM, and RTRIM remove whitespace. Clean data at insert time rather than trimming in every query.
SUBSTRING extracts portions of strings by position and length. Remember that SQL uses 1-based indexing.
LEFT and RIGHT are convenient shortcuts for extracting from the start or end of a string.
REPLACE swaps all occurrences of a substring. Chain multiple calls for multiple replacements.
POSITION (or LOCATE, CHARINDEX, INSTR) finds where a substring starts. Returns 0 if not found.
Combine functions to solve real-world problems: extract email domains, generate usernames, mask sensitive data, clean phone numbers.
Avoid wrapping indexed columns in functions inside WHERE clauses. Use functional indexes instead.
Handle NULLs explicitly with COALESCE because most string functions return NULL when given NULL input.

Master these functions and you will handle the vast majority of text manipulation tasks without leaving SQL.