SQL String Functions – UPPER, LOWER, SUBSTRING, CONCAT

UPPER() and LOWER()

UPPER() converts a string to all uppercase; LOWER() converts it to all lowercase. Both are supported identically in MySQL, PostgreSQL, and SQLite.

SQL

SELECT UPPER('hello world');     -- 'HELLO WORLD'
SELECT LOWER('SQL Is FUN');      -- 'sql is fun'

-- Practical: normalise email comparison
SELECT *
FROM users
WHERE LOWER(email) = LOWER('User@Example.COM');

-- Format display names
SELECT
  UPPER(LEFT(first_name, 1)) || LOWER(SUBSTRING(first_name, 2)) AS formatted_name
FROM employees;

💡

Case-insensitive searches

Wrap both sides in LOWER() for case-insensitive comparisons instead of relying on database collation settings — this works consistently across all databases.

LENGTH() and CHAR_LENGTH()

LENGTH() returns the number of characters (or bytes, depending on dialect). CHAR_LENGTH() always returns the character count regardless of encoding — prefer it when working with multi-byte Unicode characters.

SQL

SELECT LENGTH('Hello');          -- 5
SELECT CHAR_LENGTH('Hello');     -- 5

-- Find products with names longer than 30 characters
SELECT product_name, CHAR_LENGTH(product_name) AS name_len
FROM products
WHERE CHAR_LENGTH(product_name) > 30
ORDER BY name_len DESC;

-- Validate phone number format (must be exactly 10 digits)
SELECT phone
FROM contacts
WHERE LENGTH(REPLACE(phone, '-', '')) != 10;

SUBSTRING()

SUBSTRING(str, start, length) extracts a portion of a string. Note: SQL string positions start at 1, not 0.

SQL

SELECT SUBSTRING('Hello World', 1, 5);   -- 'Hello'
SELECT SUBSTRING('Hello World', 7);       -- 'World' (no length = rest of string)
SELECT SUBSTR('Hello World', 7, 5);       -- 'World' (SUBSTR is an alias)

-- Extract year from an ISO date string
SELECT order_id, SUBSTRING(order_date, 1, 4) AS order_year
FROM orders;

-- Get domain from email
SELECT
  email,
  SUBSTRING(email, POSITION('@' IN email) + 1) AS domain
FROM users;

ℹ️

LEFT() and RIGHT() shortcuts

LEFT(str, n) extracts the first n characters; RIGHT(str, n) extracts the last n. They are available in MySQL and PostgreSQL as convenient alternatives to SUBSTRING.

SQL

SELECT LEFT('Hello World', 5);    -- 'Hello'
SELECT RIGHT('Hello World', 5);   -- 'World'

-- Extract country code from phone
SELECT RIGHT(phone, 4) AS last_four
FROM customers;

CONCAT()

CONCAT combines two or more strings into one. MySQL uses the CONCAT() function; PostgreSQL and SQLite support the || operator (which is also valid in MySQL 8+).

SQL

-- MySQL / PostgreSQL / SQLite function style
SELECT CONCAT('Hello', ' ', 'World');    -- 'Hello World'

-- PostgreSQL / SQLite operator style
SELECT 'Hello' || ' ' || 'World';        -- 'Hello World'

-- Build full name from separate columns
SELECT
  CONCAT(first_name, ' ', last_name) AS full_name,
  CONCAT(city, ', ', country)        AS location
FROM employees;

-- MySQL CONCAT_WS: join with separator (skips NULLs automatically)
SELECT CONCAT_WS(', ', street, city, state, zip) AS address
FROM addresses;

TRIM, LTRIM, RTRIM

Trim functions remove leading and/or trailing characters (whitespace by default) from a string. Essential for cleaning user-entered data.

SQL

SELECT TRIM('  hello  ');         -- 'hello'
SELECT LTRIM('  hello  ');        -- 'hello  ' (left only)
SELECT RTRIM('  hello  ');        -- '  hello' (right only)

-- Remove a specific character (PostgreSQL / MySQL)
SELECT TRIM(LEADING '0' FROM '0001234');    -- '1234'
SELECT TRIM(TRAILING '.' FROM 'price.');    -- 'price'
SELECT TRIM(BOTH '*' FROM '***sale***');    -- 'sale'

-- Practical: clean up imported data
UPDATE products
SET product_name = TRIM(product_name)
WHERE product_name != TRIM(product_name);

REPLACE()

REPLACE(str, find, replacement) replaces every occurrence of find in str with replacement. The replacement can be an empty string to delete characters.

SQL

SELECT REPLACE('Hello World', 'World', 'SQL');  -- 'Hello SQL'
SELECT REPLACE('2026-06-08', '-', '/');          -- '2026/06/08'

-- Remove all spaces
SELECT REPLACE('hello world', ' ', '');          -- 'helloworld'

-- Standardise phone numbers: remove dashes and spaces
SELECT REPLACE(REPLACE(phone, '-', ''), ' ', '') AS clean_phone
FROM contacts;

-- Mask sensitive data (keep last 4 digits)
SELECT
  CONCAT(REPEAT('*', LENGTH(card_number) - 4),
         RIGHT(card_number, 4)) AS masked_card
FROM payments;

POSITION() / INSTR() / STRPOS()

These functions find the starting position of a substring within a string. They return 0 (MySQL) or 0/NULL depending on dialect when not found.

SQL

-- ANSI SQL (MySQL, PostgreSQL)
SELECT POSITION('@' IN 'user@example.com');   -- 5

-- MySQL / SQLite alternative
SELECT INSTR('user@example.com', '@');        -- 5

-- PostgreSQL alternative
SELECT STRPOS('user@example.com', '@');       -- 5

-- Find emails that contain a sub-domain
SELECT email
FROM users
WHERE POSITION('.' IN SUBSTRING(email, POSITION('@' IN email))) > 0;

-- Return 0 if not found
SELECT POSITION('xyz' IN 'Hello World');      -- 0 (not found)

Dialect Comparison Table

Function	MySQL	PostgreSQL	SQLite
Uppercase	`UPPER()`	`UPPER()`	`UPPER()`
Lowercase	`LOWER()`	`LOWER()`	`LOWER()`
String length	`CHAR_LENGTH()`	`LENGTH()`	`LENGTH()`
Extract substring	`SUBSTRING()`	`SUBSTRING()`	`SUBSTR()`
Concatenate	`CONCAT()`	`\|\|` or `CONCAT()`	`\|\|`
Trim whitespace	`TRIM()`	`TRIM()`	`TRIM()`
Find position	`INSTR()`	`STRPOS()`	`INSTR()`
First N chars	`LEFT()`	`LEFT()`	`SUBSTR(s,1,n)`
Last N chars	`RIGHT()`	`RIGHT()`	`SUBSTR(s,-n)`
Repeat string	`REPEAT()`	`REPEAT()`	No built-in

Summary

String Functions: Clean and Reshape Text in Queries

SQL can transform text right in the query — no need to pull rows into application code first. The everyday toolkit:

Function	Does	Example → result
`UPPER / LOWER`	change case	`UPPER('ab')` → `AB`
`LENGTH`	character count	`LENGTH('abc')` → 3
`TRIM`	strip surrounding spaces	`TRIM(' x ')` → `x`
`SUBSTRING`	extract part	`SUBSTRING('abcd',1,2)` → `ab`
`REPLACE`	swap text	`REPLACE('a-b','-','_')` → `a_b`
`CONCAT` / `\|\|`	join strings	`'a' \|\| 'b'` → `ab`

-- normalize for a case-insensitive search / dedupe
SELECT DISTINCT LOWER(TRIM(email)) AS clean_email FROM users;

-- build a full name
SELECT first_name || ' ' || last_name AS full_name FROM users;

Two gotchas: string positions are 1-based in SQL, not 0-based like most languages. And concatenating a NULL with || yields NULL in standard SQL — wrap nullable parts in COALESCE(col, ''). Exact function names vary a little by database (SUBSTR vs SUBSTRING), but the concepts are universal.

🏋️ Practical Exercise

Concatenate strings with || or CONCAT.
Change case with UPPER/LOWER.
Get a string length.
Extract a substring.
Trim whitespace with TRIM.

🔥 Challenge Exercise

Build a full_name from first_name and last_name, uppercase the initials, and extract the domain from an email using string functions. Explain common cross-database differences (|| vs CONCAT, SUBSTRING vs SUBSTR).

📋 Summary

UPPER() / LOWER() — Convert case. Use for case-insensitive comparisons.
LENGTH() / CHAR_LENGTH() — Count characters. Prefer CHAR_LENGTH() for Unicode safety.
SUBSTRING(str, start, len) — Extract a portion. SQL positions start at 1.
CONCAT() / || — Join strings. Use CONCAT_WS() to join with separator and skip NULLs.
TRIM / LTRIM / RTRIM — Remove leading/trailing characters. Great for data cleaning.
REPLACE(str, find, new) — Replace all occurrences. Pass empty string to delete.
POSITION / INSTR / STRPOS — Find substring position. Returns 0 if not found.
LEFT(str, n) / RIGHT(str, n) — Shorthand for extracting from start or end.

Interview Questions

Name five common string functions.
How do you concatenate strings in SQL?
How do you extract a substring?
How do you change a string’s case?
How does string concatenation differ across databases?

FAQ

What is the difference between LENGTH() and CHAR_LENGTH()?+

In MySQL, LENGTH() returns the byte length of the string, while CHAR_LENGTH() returns the character count. For ASCII text they are the same. For multi-byte characters (e.g. Chinese, Arabic, emoji), LENGTH() may return a larger number. In PostgreSQL and SQLite, LENGTH() always returns character count. Use CHAR_LENGTH() in MySQL to be safe with Unicode data.

Does CONCAT() handle NULL values?+

In MySQL, CONCAT() returns NULL if any argument is NULL. To handle NULLs gracefully, use CONCAT_WS(separator, ...) which skips NULL arguments, or wrap each argument in COALESCE(col, ''). In PostgreSQL, the || operator also returns NULL if either operand is NULL, so use COALESCE() around nullable columns.

Are string function names case-sensitive in SQL?+

No — SQL keywords and built-in function names are case-insensitive. UPPER(), upper(), and Upper() all work. By convention, function names are written in uppercase to distinguish them from column or table names, but the database treats them the same way.

Why does SUBSTRING start at position 1 and not 0?+

SQL uses 1-based string indexing by the ANSI standard — the first character is at position 1. This differs from most programming languages (Python, JavaScript) which use 0-based indexing. If you pass 0 as the start position in MySQL it is treated as 1; in PostgreSQL it is treated as returning from the start of the string.

UPPER() and LOWER()

LENGTH() and CHAR_LENGTH()

SUBSTRING()

CONCAT()

TRIM, LTRIM, RTRIM

REPLACE()

POSITION() / INSTR() / STRPOS()

Dialect Comparison Table

Summary

String Functions: Clean and Reshape Text in Queries

🏋️ Practical Exercise

🔥 Challenge Exercise

📋 Summary

Interview Questions

Related Topics

FAQ