Tutorials›SQL›String Functions
🟢 Free Demo
SQL TutorialTopic 17 of 20

String Functions

UPPER, LOWER, CONCAT, TRIM, LEN, SUBSTRING

✅ What You Will Learn

›How UPPER() and LOWER() standardise text case
›How LENGTH() counts characters in a string
›How CONCAT() joins strings together
›How SUBSTRING() / SUBSTR() extracts part of a string
›How TRIM() removes leading and trailing spaces
›How REPLACE() substitutes text within a string

String functions manipulate text data. In real analytics work, raw data is often inconsistent — names in different cases, extra spaces in addresses, email addresses mixed with phone numbers. String functions let you clean and transform text data directly in SQL.

📋 customers table — string functions operate on name, city, email columns

customer_idcustomer_namecityemail
1rahul sharmaDelhirahul@gmail.com
2PRIYA VERMANoidapriya@yahoo.com
3 Amit Kumar Gurgaonamit@gmail.com
4Sneha KapoorDelhisneha@outlook.com
5vikram singhNoidavikram@gmail.com

Syntax

SQL SYNTAX
UPPER(text)              -- convert to uppercase
LOWER(text)              -- convert to lowercase
TRIM(text)               -- remove leading/trailing spaces
LEN(text)                -- length of string (SQL Server)
LENGTH(text)             -- length of string (MySQL/PostgreSQL)
CONCAT(a, b, c)          -- join strings together
SUBSTRING(text, start, length)  -- extract part of string
REPLACE(text, old, new)  -- replace text within string

Examples

Example 1Clean inconsistent name casing
SELECT UPPER(customer_name) AS name_uppercase,
       LOWER(customer_name) AS name_lowercase,
       TRIM(customer_name)  AS name_trimmed
FROM customers;
OUTPUT
name_uppercase | name_lowercase | name_trimmed
---------------|----------------|-------------
RAHUL SHARMA   | rahul sharma   | Rahul Sharma
Example 2CONCAT — build full address
SELECT customer_name,
       CONCAT(city, ', ', state, ' - ', pincode) AS full_address
FROM customers;
OUTPUT
customer_name | full_address
--------------|---------------------
Rahul Sharma  | Noida, UP - 201301
Example 3SUBSTRING and REPLACE
-- Extract first 3 characters of product name
SELECT SUBSTRING(product, 1, 3) AS product_code,
       product

-- Replace underscores with spaces in category names
SELECT REPLACE(category_code, '_', ' ') AS category
FROM products;
Example 4LIKE — pattern matching in WHERE
-- Find all customers whose name starts with 'R'
SELECT customer_name FROM customers
WHERE customer_name LIKE 'R%';

-- Names containing 'Sharma'
WHERE customer_name LIKE '%Sharma%';

-- Names that are exactly 10 characters long
WHERE customer_name LIKE '__________';
💡

% matches any number of characters. _ matches exactly one character.

📌 Key Points to Remember

  • ✓UPPER and LOWER are useful for standardising case before comparisons
  • ✓TRIM removes invisible spaces that cause mismatches
  • ✓CONCAT joins multiple text values into one
  • ✓LIKE with % is the most common pattern — used in search filters everywhere
  • ✓Always clean text data before grouping or joining on text columns

🏢 Real-World Application

String functions are used constantly in data cleaning and reporting. Customer names stored inconsistently ("rahul sharma", "RAHUL SHARMA", " Rahul Sharma ") are normalised using LOWER() and TRIM(). Phone numbers stored with and without country codes are standardised using REPLACE(phone, '+91', ''). Extracting the domain from email addresses uses SUBSTRING(email, CHARINDEX('@', email) + 1). Product codes that follow a pattern (first 3 characters = category) use LEFT(product_code, 3). Any time you work with text columns in real databases, string functions are your tools for cleaning and transforming them.

⚠️ Common Mistakes to Avoid

WRONGUsing CONCAT with NULL values
FIXCONCAT with a NULL argument returns NULL in some databases. Use COALESCE: CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, '')) to safely handle NULLs in string concatenation.
WRONGConfusing SUBSTRING index starting position
FIXIn SQL, SUBSTRING starts at position 1 (not 0 like most programming languages). SUBSTRING('Hello', 1, 3) returns 'Hel', not 'ell'.
WRONGUsing LIKE without realising it is case-sensitive
FIXLIKE is case-sensitive in most databases. WHERE name LIKE 'rahul%' may not match 'Rahul'. Use LOWER(name) LIKE 'rahul%' to ensure case-insensitive matching.

❓ Frequently Asked Questions

What are string functions in SQL?

String functions manipulate text data. Common ones include UPPER(), LOWER(), LENGTH(), CONCAT(), SUBSTRING(), TRIM(), REPLACE(), LEFT(), RIGHT(), CHARINDEX(), and LIKE for pattern matching.

How do I concatenate strings in SQL?

Use CONCAT(str1, str2) or the || operator (in PostgreSQL/SQLite): CONCAT(first_name, ' ', last_name). In SQL Server you can also use +: first_name + ' ' + last_name.

How do I extract part of a string in SQL?

Use SUBSTRING(string, start, length). SUBSTRING('New Delhi', 1, 3) returns 'New'. In MySQL you can also use LEFT(string, n) and RIGHT(string, n) for the leftmost or rightmost n characters.

How do I remove spaces from a string in SQL?

TRIM() removes leading and trailing spaces. LTRIM() removes only leading spaces. RTRIM() removes only trailing spaces. To remove all spaces including in the middle, use REPLACE(col, ' ', '').

How does LIKE work in SQL?

LIKE is used for pattern matching in WHERE clauses. % matches zero or more characters; _ matches exactly one character. WHERE email LIKE '%@gmail.com' finds all Gmail users. WHERE code LIKE 'A__' finds codes starting with A followed by exactly two characters.

✏️ Practice Exercise

Write a query to find all customers whose city starts with "N" and display their name in uppercase.

← PreviousHandling NULL ValuesNext →Date Functions
🎓 Level Up Faster

Learn SQL with Live Trainer Guidance

These tutorials give you the theory. Our live SQL course at EVIKA Academy, Noida teaches you to apply SQL on real company datasets — with a trainer who uses it daily at MakeMyTrip.