Text functions

Why do text functions matter?

Real data is typed by people, and people are not consistent. One customer writes "meena joshi", another "ANIL MEHRA", a third adds a space before the email. If you compare such values directly, "Pune" and " pune" look different, and a report counts them as two cities. Text functions let you clean and reshape text inside the query, without changing what is stored.

A function is a named tool that takes some inputs (called arguments) in brackets and gives back one value. UPPER(name) takes a name and gives it back in capital letters. In a SELECT, the function runs once for every row.

The everyday text functions

Positions in SQL start at 1, not 0 as in Python or JavaScript. SUBSTR(x, 1, 3) means "the first three characters".

Combining functions: first name and email domain

Functions can be put inside each other; the inner one runs first. To get a first name, find the space with INSTR and take everything before it. To get an email domain, find the @ and take everything after it.

SELECT name, SUBSTR(name, 1, INSTR(name, ' ') - 1) AS first_name
FROM customers;
-- Asha Verma | Asha
-- Ravi Kumar | Ravi   ... 6 rows

SELECT id, SUBSTR(email, INSTR(email, '@') + 1) AS domain
FROM signups WHERE email IS NOT NULL;
-- 2 | yahoo.co.in
-- 4 | rediffmail.com   ... (row 1 still has a capital G and a trailing space)

Cleaning messy input

The usual cleaning recipe for an email is LOWER(TRIM(email)): remove stray spaces, then make it all small letters, because email addresses are compared without case in practice. For phone numbers, strip dashes and spaces with REPLACE, nested twice.

SELECT id, LOWER(TRIM(email)) AS clean_email,
       REPLACE(REPLACE(phone, '-', ''), ' ', '') AS phone_digits
FROM signups;
-- 1 | rohan.das@gmail.com | 9845012345
-- 3 | anil@outlook.com    | 9123456789
-- 5 | NULL                | 9000011111

|| and CONCAT in different databases

|| is the SQL standard way to join text, and SQLite, PostgreSQL and Oracle use it. MySQL treats || as OR unless a setting is changed, so MySQL code uses CONCAT('Asha', ' ', 'Verma'). SQL Server joins text with + or CONCAT. SQLite 3.44 and later also has concat().

Common mistakes

• A name with no space: INSTR returns 0, so SUBSTR(name, 1, 0 − 1) gives an empty text. Check with CASE (topic 4) when single names are possible.
• Counting from 0. SQL positions start at 1, so SUBSTR(x, 0, 3) is not the first three characters. Use SUBSTR(x, 1, 3).
• Forgetting TRIM before comparing. ' Pune' = 'Pune' is false. Clean both sides.
• Using LENGTH in MySQL for Hindi or other non-English text: it counts bytes, not letters. Use CHAR_LENGTH there.
• Joining with || when a piece can be NULL, and getting NULL for the whole value. Wrap the piece in COALESCE.