String Functions

Text in a Database, and Why the Type Matters

Before the functions, a short detour into how text is actually stored, because two of the most confusing string bugs come from the storage rather than from any function you call.

CHAR(n) is fixed length: MySQL pads shorter values with spaces up to n and strips them again on retrieval, which sounds harmless right up until you compare or concatenate. VARCHAR(n) stores only what you put in, up to a limit of n, and is the right default for names, emails and addresses. TEXT is for long content with no sensible upper bound — a description, a review body — and in MySQL it cannot carry a default value and can only be indexed with a prefix length.

The character set decides which characters can be stored at all. If you are storing Indian-language text or emoji in MySQL, that character set must be utf8mb4. The older set confusingly named utf8 in MySQL is not real UTF-8 — it handles at most three bytes per character and fails on four-byte characters, which includes every emoji. utf8mb4 has been the default since MySQL 8.0, but a database created on an older version may still be on the wrong one, so check rather than assume.

The collation decides how text is compared and sorted. MySQL's common collations end in _ci, meaning case-insensitive, which is why WHERE email = 'ANANYA@EXAMPLE.COM' often matches a row stored in lowercase. PostgreSQL is case-sensitive by default and the same query finds nothing at all. This is one of the most surprising differences between the two, and it means a login query that works perfectly on MySQL can fail on PostgreSQL without a single character of the SQL changing.

-- Check what a MySQL database is actually using
SELECT @@character_set_database, @@collation_database;
SHOW CREATE TABLE students;

-- A table that can hold Indian-language text and emoji
CREATE TABLE reviews (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    body       TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- On a case-insensitive collation (MySQL's default) this matches
SELECT name FROM students WHERE email = 'ANANYA@EXAMPLE.COM';

-- Force a case-sensitive comparison in MySQL when you need one
SELECT name FROM students WHERE BINARY email = 'ananya@example.com';

-- PostgreSQL is case-sensitive, so you ask for insensitivity explicitly
-- SELECT name FROM students
-- WHERE LOWER(email) = LOWER('ANANYA@EXAMPLE.COM');

Joining Strings Together

String concatenation is where the dialects diverge immediately, and there is genuinely no spelling that works everywhere.

MySQL uses the function CONCAT(a, b, c), which accepts any number of arguments. PostgreSQL, Oracle and SQLite use the || operator and also support CONCAT. SQL Server uses +, and has supported CONCAT since SQL Server 2012. That makes CONCAT the closest thing to a portable choice, and it is the one to write by default.

Do not use || in MySQL. There it means logical OR, not concatenation, so SELECT 'a' || 'b' returns 0 rather than 'ab' and raises no error at all. The behaviour can be switched with the PIPES_AS_CONCAT SQL mode, which is exactly the kind of server-level setting that makes identical code behave differently on two machines.

Now the trap that really bites. In MySQL, CONCAT returns NULL if any argument is NULL. Build a full address out of five columns and one missing line wipes out the entire string — the row shows nothing rather than the four parts you did have. PostgreSQL's and SQL Server's CONCAT functions behave differently and treat NULL as an empty string, while PostgreSQL's || operator does return NULL if either side is NULL. The habit that is safe everywhere is to wrap nullable columns in COALESCE.

Better still, use CONCAT_WS — concatenate with separator. It takes the separator as its first argument and then skips any NULL arguments instead of poisoning the whole result, so the separators land only between the parts that actually exist. It is available in MySQL, PostgreSQL and SQL Server 2017 and later, and it is the right tool for building addresses and names out of optional pieces.

-- MySQL: CONCAT takes any number of arguments
SELECT CONCAT(name, ' (', branch, ')') AS label FROM students;

-- One NULL wipes out the whole string in MySQL
SELECT CONCAT('Flat 3B, ', NULL, ', Pune') AS address;   -- NULL

-- Defend each nullable part with COALESCE
SELECT CONCAT(
    COALESCE(address_line1, ''), ', ',
    COALESCE(city, ''), ' ',
    COALESCE(pincode, '')
) AS address
FROM students;

-- CONCAT_WS skips NULLs and places the separator only where needed
SELECT CONCAT_WS(', ', address_line1, address_line2, city, pincode)
    AS address
FROM students;

-- Other dialects
-- PostgreSQL, Oracle, SQLite:
--   SELECT name || ' (' || branch || ')' FROM students;
-- SQL Server:
--   SELECT name + ' (' + branch + ')' FROM students;

-- In MySQL, || means OR. This returns 0, not 'ab', and does not error.
SELECT 'a' || 'b' AS surprising;

Length, Case and Whitespace

LENGTH and CHAR_LENGTH are not the same function, and on Indian-language data the gap is large. In MySQL, LENGTH returns the number of bytes and CHAR_LENGTH returns the number of characters. In UTF-8 an English letter takes one byte, a Devanagari or Tamil character takes three, and an emoji takes four. So a six-character Hindi name does not have a length of 6. When you are checking that a name fits in 50 characters, CHAR_LENGTH is the function you want. PostgreSQL uses the opposite convention — its LENGTH already counts characters and OCTET_LENGTH counts bytes — which is another reason to be explicit about which you mean.

UPPER and LOWER do exactly what they say and exist in every database. Their main use is making a comparison case-independent, though as the first section warned, doing that inside WHERE stops the index on that column from being used.

TRIM removes leading and trailing spaces, and LTRIM and RTRIM remove them from one end only. Stray whitespace is a genuinely common source of "why does this not match": a value pasted from a spreadsheet or typed with an extra space compares as different, and the difference is completely invisible on screen. When a comparison fails and the two values look identical, compare their lengths.

MySQL's TRIM can also strip a specific string rather than spaces, using the form TRIM(LEADING '0' FROM value). That is useful for cleaning up zero-padded codes imported from another system, where '000123' and '123' are supposed to be the same thing.

-- Bytes and characters are different measurements
SELECT
    LENGTH('Ananya')      AS bytes_english,       -- 6
    CHAR_LENGTH('Ananya') AS chars_english,       -- 6
    LENGTH('कक्षा')         AS bytes_devanagari,    -- far more than 4
    CHAR_LENGTH('कक्षा')    AS chars_devanagari;

-- Check a length limit in characters, not bytes
SELECT name FROM students WHERE CHAR_LENGTH(name) > 50;

-- The invisible whitespace problem
SELECT COUNT(*) AS exact_match  FROM students WHERE branch = 'CSE';
SELECT COUNT(*) AS after_trim   FROM students WHERE TRIM(branch) = 'CSE';
-- If those two disagree, some rows carry stray spaces

-- Find the offenders. Comparing lengths is reliable; comparing the
-- strings themselves is not (see the note below).
SELECT id, CONCAT('[', branch, ']') AS shown, CHAR_LENGTH(branch) AS len
FROM students
WHERE CHAR_LENGTH(branch) <> CHAR_LENGTH(TRIM(branch));

-- Fix it once, at the source
UPDATE students
SET branch = TRIM(branch)
WHERE CHAR_LENGTH(branch) <> CHAR_LENGTH(TRIM(branch));

-- Trimming something other than spaces (MySQL)
SELECT TRIM(LEADING '0' FROM '000123') AS cleaned;   -- '123'

Cutting Strings Up

SUBSTRING(str, start, length) extracts part of a string, and the single most important detail is that SQL counts from 1, not from 0. Coming from Python, Java or JavaScript this catches everybody exactly once. SUBSTRING('Ananya', 1, 3) is 'Ana'. SUBSTR is an accepted synonym in MySQL, PostgreSQL, Oracle and SQLite.

LEFT(str, n) and RIGHT(str, n) take characters from the ends, and they are simply easier to read when that is what you meant. MySQL, PostgreSQL and SQL Server all have them; Oracle uses SUBSTR with a negative starting position for the right-hand case.

To cut at a position you have to find first, you need a search function, and the names diverge again. MySQL has both LOCATE(needle, haystack) and INSTR(haystack, needle) — note that the argument order flips between them. PostgreSQL and the SQL standard have POSITION(needle IN haystack). SQL Server has CHARINDEX(needle, haystack). All of them return 1-based positions and return 0 when the substring is absent, which is convenient precisely because 0 is never a valid position.

MySQL adds SUBSTRING_INDEX, which splits on a delimiter and is genuinely handy: pulling the domain out of an email address becomes one expression instead of a nest of SUBSTRING and LOCATE. It has no direct equivalent elsewhere, so treat it as a MySQL convenience that will need rewriting if the query ever moves. REPLACE, by contrast, swaps every occurrence of one substring for another and behaves the same everywhere.

-- SQL counts from 1
SELECT SUBSTRING('Ananya Sharma', 1, 6) AS first_six;    -- 'Ananya'
SELECT SUBSTRING('Ananya Sharma', 8)    AS from_eighth;  -- 'Sharma'

-- Shorter forms for the two ends
SELECT LEFT('Ananya Sharma', 6)  AS initial_part;
SELECT RIGHT('Ananya Sharma', 6) AS final_part;

-- Find a position, then cut at it (MySQL)
SELECT
    email,
    LOCATE('@', email)                       AS at_position,
    LEFT(email, LOCATE('@', email) - 1)      AS local_part,
    SUBSTRING(email, LOCATE('@', email) + 1) AS domain
FROM students;

-- MySQL's shortcut for exactly that job
SELECT
    SUBSTRING_INDEX(email, '@', 1)  AS local_part,
    SUBSTRING_INDEX(email, '@', -1) AS domain
FROM students;

-- Which email domains are most common?
SELECT SUBSTRING_INDEX(email, '@', -1) AS domain, COUNT(*) AS students
FROM students
GROUP BY SUBSTRING_INDEX(email, '@', -1)
ORDER BY students DESC;

-- REPLACE swaps every occurrence
SELECT REPLACE('+91 98765 43210', ' ', '') AS digits_only;

-- Finding a position in the other dialects
-- PostgreSQL: POSITION('@' IN email)
-- SQL Server: CHARINDEX('@', email)

Searching Text with LIKE

LIKE is the basic text search. % matches any number of characters, including none, and _ matches exactly one. WHERE name LIKE 'An%' finds names beginning with An; LIKE '%nair%' finds the letters anywhere inside the value.

Whether LIKE is case-sensitive depends on the collation, not on the operator. MySQL's default collations are case-insensitive, so LIKE 'an%' also matches 'Ananya'. PostgreSQL's LIKE is case-sensitive and supplies ILIKE when you want the insensitive version. Never assume — test it on the database you are actually deploying to.

The performance rule is short and matters more than it looks. LIKE 'An%' can use an index on that column, because the engine knows whereabouts in the sorted index to start reading. LIKE '%an' and LIKE '%an%' cannot, because a leading wildcard means a match could begin anywhere and the only way to be certain is to examine every single row. On a few thousand rows nobody notices; on a few million it is the difference between instant and unusable, and it is exactly why search-as-you-type features are built on full-text indexes rather than on LIKE '%...%'.

To search for a literal % or _, add an ESCAPE clause and nominate an escape character. For anything beyond wildcards, MySQL offers REGEXP (also spelled RLIKE) and PostgreSQL the ~ operator, both supporting real regular expressions. They are powerful and they are slower still, because a regular expression cannot use an ordinary index at all.

-- % is any number of characters, _ is exactly one
SELECT name  FROM students WHERE name  LIKE 'An%';    -- starts with An
SELECT name  FROM students WHERE name  LIKE '%Nair';  -- ends with Nair
SELECT name  FROM students WHERE name  LIKE '_a%';    -- 'a' 2nd character
SELECT email FROM students WHERE email LIKE '%@gmail.com';

-- Can use an index on name: the engine knows where to start
SELECT name FROM students WHERE name LIKE 'An%';

-- Cannot use an index: every row has to be examined
SELECT name FROM students WHERE name LIKE '%an%';

-- Searching for a literal percent sign
SELECT title FROM offers WHERE title LIKE '%50!%%' ESCAPE '!';

-- Case sensitivity comes from the collation, not from LIKE:
--   MySQL default  -> case-insensitive
--   PostgreSQL     -> LIKE is case-sensitive, ILIKE is not

-- Regular expressions (MySQL)
SELECT name  FROM students WHERE name  REGEXP '^[AM]';
SELECT phone FROM students WHERE phone NOT REGEXP '^[0-9]{10}$';
-- PostgreSQL writes the first one as: WHERE name ~ '^[AM]'

Cleaning Data, and Where Formatting Belongs

String functions earn their keep during data cleaning, when a file has arrived from somewhere else and nothing is quite the right shape. The workflow never changes: write a SELECT that puts the current value beside the proposed new one, look at it properly, and only then convert it into an UPDATE carrying the same WHERE clause.

Do the fix once and store the clean value, rather than applying the function inside every query afterwards. A phone column cleaned to ten digits at import time can be indexed and compared directly. A phone column cleaned with REPLACE inside every WHERE clause can never use an index, and will be cleaned slightly differently by whoever writes the next query.

LPAD and RPAD pad a value out to a fixed width, which is how you turn an id into a code such as STU0042. That is a perfectly reasonable thing to compute in a query. It is usually a bad thing to store, because the padded string is no longer a number and can no longer be sorted or compared as one.

Which leads to the general principle. Presentation — currency symbols, date formats, capitalising a name for display — belongs in the application, not in the database. Storing a formatted string throws away your ability to compute with the value, and formatting rules change far more often than data does. Use string functions to clean data on the way in and to build search keys; let the application decide how it looks on the way out.

-- Step 1: look at what the change would actually do
SELECT
    id,
    phone AS current_value,
    REPLACE(REPLACE(REPLACE(phone, ' ', ''), '-', ''), '+91', '')
        AS proposed
FROM students
WHERE phone IS NOT NULL;

-- Step 2: the same expression, now as an UPDATE
UPDATE students
SET phone = REPLACE(REPLACE(REPLACE(phone, ' ', ''), '-', ''), '+91', '')
WHERE phone IS NOT NULL;

-- Normalise emails once, so a plain index works forever after
UPDATE students SET email = LOWER(TRIM(email));

-- Build a display code from an id: compute it, do not store it
SELECT id, CONCAT('STU', LPAD(id, 4, '0')) AS roll_code FROM students;

-- Mask an email for a support screen
SELECT
    CONCAT(LEFT(email, 2), '****', SUBSTRING(email, LOCATE('@', email)))
        AS masked_email
FROM students;

-- Find rows that need attention before anybody else does
SELECT id, name FROM students
WHERE CHAR_LENGTH(name) <> CHAR_LENGTH(TRIM(name));

SELECT id, phone FROM students
WHERE phone IS NOT NULL AND CHAR_LENGTH(phone) <> 10;