Skip to main content

SQL Built-In String Functions

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_idfull_name
1Alice Martin
2bob JONES
3Carol Smith
4Dave O'Brien
5Eve 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_nameemailname_lenemail_len
Alicealice.martin@example.com524
bobBOB.JONES@CORP.COM318
Carolcarol_smith@gmail.com521
Davedave.obrien@example.com423
Eveeve@oldcompany.com318

Finding unusually long or short values:

-- Emails longer than 22 characters
SELECT email, LENGTH(email) AS len
FROM contacts
WHERE LENGTH(email) > 22;
emaillen
alice.martin@example.com24
dave.obrien@example.com23
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_namelast_nameupper_namelower_email
AliceMartinALICEalice.martin@example.com
bobJONESBOBbob.jones@corp.com
CarolSmithCAROLcarol_smith@gmail.com
DaveO'BrienDAVEdave.obrien@example.com
EveTurnerEVEeve@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;
cityoriginal_lentrimmedtrimmed_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_trimmedright_trimmedboth_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)
info

String positions in SQL start at 1, not 0. The first character is position 1.

Examples​

Extract the first 3 characters:

SELECT
    first_name,
    SUBSTRING(first_name, 1, 3) AS abbreviation
FROM contacts;
first_nameabbreviation
AliceAli
bobbob
CarolCar
DaveDav
EveEve

Extract email domain:

SELECT
    email,
    SUBSTRING(email, POSITION('@' IN email) + 1) AS domain
FROM contacts;
emaildomain
alice.martin@example.comexample.com
BOB.JONES@CORP.COMCORP.COM
carol_smith@gmail.comgmail.com
dave.obrien@example.comexample.com
eve@oldcompany.comoldcompany.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;
emailusername
alice.martin@example.comalice.martin
BOB.JONES@CORP.COMBOB.JONES
carol_smith@gmail.comcarol_smith
dave.obrien@example.comdave.obrien
eve@oldcompany.comeve

Extract area code from phone:

SELECT
    phone,
    SUBSTRING(phone, 1, 3) AS area_code
FROM contacts
WHERE phone IS NOT NULL;
phonearea_code
555-0101555
555-0102555
555-0103555
555-0105555

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;
emailfirst_fivelast_four
alice.martin@example.comalice.com
BOB.JONES@CORP.COMBOB.J.COM
carol_smith@gmail.comcarol.com
dave.obrien@example.comdave..com
eve@oldcompany.comeve@o.com

Practical use: masking sensitive data:

SELECT
    email,
    LEFT(email, 2) || '***' || SUBSTRING(email, POSITION('@' IN email)) AS masked_email
FROM contacts;
emailmasked_email
alice.martin@example.comal***@example.com
BOB.JONES@CORP.COMBO***@CORP.COM
carol_smith@gmail.comca***@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;
phoneclean_phone
555-01015550101
555-01025550102
555-01035550103
555-01055550105

Update email domains:

SELECT
    email,
    REPLACE(email, 'oldcompany.com', 'newcompany.com') AS updated_email
FROM contacts;
emailupdated_email
alice.martin@example.comalice.martin@example.com
BOB.JONES@CORP.COMBOB.JONES@CORP.COM
carol_smith@gmail.comcarol_smith@gmail.com
dave.obrien@example.comdave.obrien@example.com
eve@oldcompany.comeve@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;
emailat_position
alice.martin@example.com13
BOB.JONES@CORP.COM10
carol_smith@gmail.com12
dave.obrien@example.com12
eve@oldcompany.com4

Check if a substring exists:

-- Find contacts with Gmail addresses
SELECT first_name, email
FROM contacts
WHERE POSITION('gmail' IN LOWER(email)) > 0;
first_nameemail
Carolcarol_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;
emailfirst_part
alice.martin@example.comalice
BOB.JONES@CORP.COMBOB
dave.obrien@example.comdave
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_idfirst_namelast_name
1AliceMartin
2BobJones
3CarolSmith
4DaveO'brien
5EveTurner
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;
emaildomainprovider_type
alice.martin@example.comexample.comExample
BOB.JONES@CORP.COMcorp.comCorporate
carol_smith@gmail.comgmail.comGmail
dave.obrien@example.comexample.comExample
eve@oldcompany.comoldcompany.comOther

Generate a Username from Full Name​

SELECT
    first_name,
    last_name,
    LOWER(
        CONCAT(
            LEFT(first_name, 1),
            last_name
        )
    ) AS username
FROM contacts;
first_namelast_nameusername
AliceMartinamartin
bobJONESbjones
CarolSmithcsmith
DaveO'Briendo'brien
EveTurnereturner

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;
phoneformatted_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_idfirst_namenotesmention_pos
3CarolReferred by Alice Martin13

Database Compatibility Reference​

FunctionPostgreSQLMySQLSQL ServerOracleSQLite
CONCAT()YesYesYesYes (12c+)No (use ||)
|| operatorYesWith modeNo (use +)YesYes
CONCAT_WS()YesYesYes (2017+)NoNo
LENGTH()YesYesUse LEN()YesYes
UPPER() / LOWER()YesYesYesYesYes
TRIM()YesYesUse LTRIM/RTRIMYesYes
SUBSTRING()YesYesYesUse SUBSTR()Use SUBSTR()
LEFT() / RIGHT()YesYesYesUse SUBSTR()Yes
REPLACE()YesYesYesYesYes
POSITION()YesUse LOCATE()Use CHARINDEX()Use INSTR()Use INSTR()
INITCAP()YesNoNoYesNo
LPAD() / RPAD()YesYesNoYesNo
REVERSE()YesYesYesNoNo
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_leftpadded_right
000042Hi........

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
warning

Every time you wrap a column in a function inside a WHERE clause, the database must apply that function to every row before comparing. On large tables, this turns an instant index lookup into a full table scan. Use functional indexes or store data in a normalized format to avoid this.

Quick Reference​

TaskFunctionExample
Combine stringsCONCAT() or ||CONCAT(first, ' ', last)
String lengthLENGTH() / LEN()LENGTH('Hello') → 5
UppercaseUPPER()UPPER('hello') → 'HELLO'
LowercaseLOWER()LOWER('HELLO') → 'hello'
Remove spacesTRIM()TRIM(' hi ') → 'hi'
Left trimLTRIM()LTRIM(' hi') → 'hi'
Right trimRTRIM()RTRIM('hi ') → 'hi'
Extract substringSUBSTRING()SUBSTRING('Hello', 1, 3) → 'Hel'
First N charsLEFT()LEFT('Hello', 3) → 'Hel'
Last N charsRIGHT()RIGHT('Hello', 3) → 'llo'
Find positionPOSITION()POSITION('@' IN email)
Replace textREPLACE()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.

Table of Contents