Working with Text - String Functions

Written with a little help from AI, reviewed and edited by a human.

When working with text data, you often need to manipulate strings — like converting to uppercase, extracting part of a string, or combining multiple strings together. SQL provides string functions to help you do this.

Real life example

Imagine you have a filing cabinet full of student records. Each record has a name written on it. Sometimes you need to:

• Convert all names to UPPERCASE for consistency
• Extract just the first name from “John Doe”
• Combine first name and last name into a full name
• Find how many characters are in a name

String functions let you do all of this directly in your SQL queries.

UPPER and LOWER

Convert text to uppercase or lowercase:

id	name
1	Alice
2	Bob
3	CHARLIE

SELECT
  name,
  UPPER(name) AS uppercase,
  LOWER(name) AS lowercase
FROM users;

Result:

name	uppercase	lowercase
Alice	ALICE	alice
Bob	BOB	bob
CHARLIE	CHARLIE	charlie

This is useful when you want case-insensitive comparisons or consistent formatting.

LENGTH

Get the number of characters in a string:

SELECT name, LENGTH(name) AS name_length
FROM users;

Result:

name	name_length
Alice	5
Bob	3
CHARLIE	7

You can use this to filter by length:

SELECT * FROM users
WHERE LENGTH(name) > 5;

This returns only users whose names are longer than 5 characters.

TRIM, LTRIM, RTRIM

Remove whitespace from the beginning and/or end of a string:

• TRIM — removes spaces from both ends
• LTRIM — removes spaces from the left (beginning)
• RTRIM — removes spaces from the right (end)

SELECT
  '  Alice  ' AS original,
  TRIM('  Alice  ') AS trimmed,
  LTRIM('  Alice  ') AS left_trimmed,
  RTRIM('  Alice  ') AS right_trimmed;

Result:

original	trimmed	left_trimmed	right_trimmed
Alice	Alice	Alice	Alice

This is useful when cleaning up user input that might have accidental spaces.

CONCAT

Combine multiple strings into one:

id	first_name	last_name
1	Alice	Johnson
2	Bob	Smith

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

Result:

first_name	last_name	full_name
Alice	Johnson	Alice Johnson
Bob	Smith	Bob Smith

You can also use the || operator in many databases:

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

SUBSTRING (or SUBSTR)

Extract part of a string:

SELECT
  name,
  SUBSTRING(name, 1, 3) AS first_three
FROM users;

Result:

name	first_three
Alice	Ali
Bob	Bob
CHARLIE	CHA

SUBSTRING(name, 1, 3) means: “Starting at position 1, take 3 characters.”

Note: In SQL, string positions start at 1, not 0 (unlike most programming languages).

You can also extract from a specific position to the end:

SUBSTRING(name, 3)  -- Everything from position 3 onward

For “Alice”, this would return “ice”.

REPLACE

Replace all occurrences of a substring with another string:

SELECT
  email,
  REPLACE(email, '@example.com', '@newdomain.com') AS new_email
FROM users;

If the email is alice@example.com, the result would be alice@newdomain.com.

This is useful for bulk text replacements.

INSTR (or POSITION)

Find the position of a substring within a string:

SELECT
  email,
  INSTR(email, '@') AS at_position
FROM users;

For alice@example.com, this returns 6 (the position of the @ symbol).

If the substring is not found, it returns 0.

Combining functions

You can combine multiple string functions:

Extract the part of an email before the @:

SELECT
  email,
  SUBSTRING(email, 1, INSTR(email, '@') - 1) AS username
FROM users;

For alice@example.com, this returns alice.

Here’s how it works:

1. INSTR(email, '@') finds the position of @ (position 6)
2. SUBSTRING(email, 1, 5) extracts characters 1 through 5
3. Result: alice

COALESCE with strings

You can use COALESCE to provide default text for NULL values:

SELECT
  name,
  COALESCE(phone, 'No phone') AS phone_display
FROM users;

If phone is NULL, it shows “No phone” instead.

Case-insensitive searching

String functions are useful for case-insensitive searches:

SELECT * FROM users
WHERE LOWER(name) = LOWER('alice');

This finds “Alice”, “ALICE”, “alice”, etc. — any capitalization.

Common use cases

Formatting names for display:

SELECT CONCAT(UPPER(SUBSTRING(first_name, 1, 1)), LOWER(SUBSTRING(first_name, 2))) AS formatted_name
FROM users;

This capitalizes the first letter and lowercases the rest: “ALICE” → “Alice”.

Extracting domain from email:

SELECT SUBSTRING(email, INSTR(email, '@') + 1) AS domain
FROM users;

For alice@example.com, this returns example.com.

Cleaning up whitespace:

UPDATE users
SET name = TRIM(name);

This removes accidental spaces from all names.

Database differences

String function names vary slightly between databases:

• SUBSTRING vs SUBSTR (both common)
• INSTR (SQLite, Oracle) vs POSITION (PostgreSQL) vs CHARINDEX (SQL Server)
• CONCAT vs || operator (both work in most databases)
• LENGTH (SQLite, PostgreSQL) vs LEN (SQL Server)

Check your database’s documentation for the exact function names.

Summary

• UPPER/LOWER — Convert case
• LENGTH — Get string length
• TRIM — Remove whitespace
• CONCAT — Combine strings
• SUBSTRING — Extract part of a string
• REPLACE — Replace text
• INSTR/POSITION — Find substring position

String functions let you clean, format, and manipulate text directly in SQL instead of having to do it in your application code.