Chapter 25 of 35

String Functions

On this page

A huge part of real-world data is text: names, emails, cities, phone numbers, product codes, addresses. And text data is messy. Names arrive as " rahul VERMA ", phone numbers as "+91 98765-43210", and reports want "first name only", "email domain", or order codes like ORD-001001.

SQL's string functions let you do all of this directly in a query: join strings, cut out parts, search inside them, change case, trim spaces, pad, replace and even use regular expressions. In this chapter we will go through the string functions you will actually use in MySQL, with the classic interview tasks (split a full name, extract an email domain, mask an email) and the traps around NULL, bytes vs characters, and performance.

All examples use the shared company_db sample database, and every output shown is real MySQL 8.4 output.


Before We Start: Three Rules

  • Positions start at 1, not 0. In 'Pune', P is position 1. (This trips up C, Java and Python programmers.)

  • Functions return a new value; they never change the table. SELECT UPPER(name) only changes what is displayed. To change stored data, use the function inside an UPDATE.

  • Almost every string function returns NULL if an argument is NULL. Meera's email is NULL, so UPPER(email), REPLACE(email, ...) and so on give NULL for her. We will see the one important exception, CONCAT_WS.

A quick map of what is coming:

Task

Functions

Join strings

CONCAT, CONCAT_WS, GROUP_CONCAT

Length

CHAR_LENGTH, LENGTH

Change case

UPPER/UCASE, LOWER/LCASE

Extract part

SUBSTRING/SUBSTR, LEFT, RIGHT, SUBSTRING_INDEX

Search

LOCATE, INSTR, POSITION

Clean up

TRIM, LTRIM, RTRIM, REPLACE

Pad / build

LPAD, RPAD, REPEAT, SPACE, REVERSE

Format / compare

FORMAT, STRCMP, ASCII, CHAR

Patterns

REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR


Joining Strings: CONCAT and CONCAT_WS

CONCAT

CONCAT(s1, s2, ...) joins any number of strings end to end. (MySQL does not use + for strings, and || means logical OR by default, so CONCAT is the way.)

SELECT emp_id, CONCAT(name, ' (', city, ')') AS name_city
FROM employees
WHERE emp_id IN (1, 10);
+--------+--------------------------+
| emp_id | name_city                |
+--------+--------------------------+
|      1 | Aarav Sharma (Bengaluru) |
|     10 | NULL                     |
+--------+--------------------------+
2 rows in set (0.00 sec)

Ishita's city is NULL, and one NULL argument makes the entire CONCAT NULL. Her name is lost too. Protect nullable columns with COALESCE(city, 'N/A') (NULL Values chapter), or use CONCAT_WS.

CONCAT_WS: with a separator, skipping NULLs

CONCAT_WS(separator, s1, s2, ...) ("concat with separator") puts the separator between the values and skips NULL values instead of returning NULL:

SELECT name,
       CONCAT('Email: ', email)            AS c1,
       CONCAT_WS(' | ', name, email, city) AS c2
FROM employees
WHERE emp_id IN (7, 8, 10);
+--------------+---------------------------+-----------------------------------------+
| name         | c1                        | c2                                      |
+--------------+---------------------------+-----------------------------------------+
| Karan Patel  | Email: karan@company.com  | Karan Patel | karan@company.com | Delhi |
| Meera Nair   | NULL                      | Meera Nair | Kochi                      |
| Ishita Gupta | Email: ishita@company.com | Ishita Gupta | ishita@company.com       |
+--------------+---------------------------+-----------------------------------------+
3 rows in set (0.00 sec)

Meera (no email) and Ishita (no city) still get a sensible result, without a dangling separator. This makes CONCAT_WS ideal for addresses and CSV-style output. Only a NULL separator makes CONCAT_WS return NULL.


Length: CHAR_LENGTH vs LENGTH

This is a classic interview question with a very Indian twist:

  • CHAR_LENGTH(s) (or CHARACTER_LENGTH) counts characters.

  • LENGTH(s) counts bytes.

For plain English text they are equal, because each letter takes one byte in UTF-8. But MySQL 8 stores text as utf8mb4, where the rupee sign and Devanagari letters take 3 bytes each:

SELECT LENGTH('SQL')         AS len_ascii,
       CHAR_LENGTH('SQL')    AS chars_ascii,
       LENGTH('₹500')        AS len_rupee,
       CHAR_LENGTH('₹500')   AS chars_rupee,
       LENGTH('नमस्ते')      AS len_hindi,
       CHAR_LENGTH('नमस्ते') AS chars_hindi;
+-----------+-------------+-----------+-------------+-----------+-------------+
| len_ascii | chars_ascii | len_rupee | chars_rupee | len_hindi | chars_hindi |
+-----------+-------------+-----------+-------------+-----------+-------------+
|         3 |           3 |         6 |           4 |        18 |           6 |
+-----------+-------------+-----------+-------------+-----------+-------------+
1 row in set (0.00 sec)
'₹500'   = ₹ (3 bytes) + 5 + 0 + 0 (1 byte each)  = 4 characters, 6 bytes
'नमस्ते'  = न म स ् त े  (6 code points, 3 bytes each)  = 6 "characters", 18 bytes

(For Hindi, CHAR_LENGTH counts Unicode code points. नमस्ते looks like 3 or 4 letters to a reader but is 6 code points, because vowel signs and the halant are separate code points.)

So when you validate "name must be at most 50 characters" (which is what VARCHAR(50) means), use CHAR_LENGTH. Use LENGTH only when you truly care about storage bytes.

Length is useful in ORDER BY and WHERE too. The longest names:

SELECT name, CHAR_LENGTH(name) AS chars
FROM employees
ORDER BY chars DESC, name
LIMIT 3;
+--------------+-------+
| name         | chars |
+--------------+-------+
| Aarav Sharma |    12 |
| Ishita Gupta |    12 |
| Sneha Kapoor |    12 |
+--------------+-------+
3 rows in set (0.00 sec)

Changing Case: UPPER and LOWER

SELECT UPPER(name) AS upper_name, LOWER(email) AS lower_email
FROM employees
WHERE emp_id = 2;
+------------+-------------------+
| upper_name | lower_email       |
+------------+-------------------+
| PRIYA IYER | priya@company.com |
+------------+-------------------+
1 row in set (0.00 sec)

UCASE and LCASE are synonyms. Remember from the WHERE Clause chapter that MySQL's default collation is already case-insensitive, so you do not need WHERE UPPER(city) = 'MUMBAI' to match "Mumbai"; plain city = 'mumbai' works, and it can use an index. UPPER/LOWER are mainly for displaying or normalising data before storing it.

MySQL has no built-in "title case" (INITCAP in Oracle). We will build one from smaller functions in the data-cleaning example at the end.


Extracting Parts: SUBSTRING, LEFT, RIGHT

SUBSTRING(str, pos, len) returns len characters starting at position pos. Leave out len to take everything to the end. SUBSTR and MID are synonyms.

A a r a v   S h a r m a
1 2 3 4 5 6 7 8 9 10 11 12
SELECT name,
       SUBSTRING(name, 1, 5) AS first5,
       SUBSTRING(name, 7)    AS from7,
       LEFT(name, 3)         AS left3,
       RIGHT(name, 4)        AS right4
FROM employees
WHERE emp_id = 1;
+--------------+--------+--------+-------+--------+
| name         | first5 | from7  | left3 | right4 |
+--------------+--------+--------+-------+--------+
| Aarav Sharma | Aarav  | Sharma | Aar   | arma   |
+--------------+--------+--------+-------+--------+
1 row in set (0.00 sec)

LEFT(s, n) and RIGHT(s, n) are shortcuts for the first and last n characters.

Negative positions and position 0

A negative position counts from the end of the string. Position 0 is not valid and gives an empty string:

SELECT SUBSTRING('Bengaluru', -4)    AS last4,
       SUBSTRING('Bengaluru', -4, 2) AS neg_len2,
       SUBSTRING('Bengaluru', 0)     AS from_zero,
       SUBSTR('Bengaluru', 2, 3)     AS substr_alias;
+-------+----------+-----------+--------------+
| last4 | neg_len2 | from_zero | substr_alias |
+-------+----------+-----------+--------------+
| luru  | lu       |           | eng          |
+-------+----------+-----------+--------------+
1 row in set (0.00 sec)

The empty from_zero is a classic off-by-one bug for people used to 0-based indexing: no error, just an empty result.


Searching Inside a String: LOCATE, INSTR, POSITION

All three return the position of the first occurrence of a substring, or 0 if it is not found (0 works as "not found" precisely because positions start at 1). Note the different argument orders:

Function

Argument order

LOCATE(substr, str [, start])

needle first; optional start position

INSTR(str, substr)

haystack first

POSITION(substr IN str)

standard SQL syntax

SELECT name,
       LOCATE(' ', name)       AS space_pos,
       INSTR(name, ' ')        AS instr_pos,
       POSITION(' ' IN name)   AS position_pos,
       LOCATE('xyz', name)     AS not_found
FROM employees
WHERE emp_id IN (1, 4);
+--------------+-----------+-----------+--------------+-----------+
| name         | space_pos | instr_pos | position_pos | not_found |
+--------------+-----------+-----------+--------------+-----------+
| Aarav Sharma |         6 |         6 |            6 |         0 |
| Sneha Kapoor |         6 |         6 |            6 |         0 |
+--------------+-----------+-----------+--------------+-----------+
2 rows in set (0.00 sec)

LOCATE's third argument starts the search later, which finds the second occurrence:

SELECT LOCATE('a', 'banana') AS first_a, LOCATE('a', 'banana', 3) AS a_from_3;
+---------+----------+
| first_a | a_from_3 |
+---------+----------+
|       2 |        4 |
+---------+----------+
1 row in set (0.00 sec)

Combining functions: split a full name

The real power comes from nesting functions. To split "Aarav Sharma": the first name is everything before the space, the last name everything after it.

SELECT name,
       LEFT(name, LOCATE(' ', name) - 1)      AS first_name,
       SUBSTRING(name, LOCATE(' ', name) + 1) AS last_name
FROM employees
WHERE emp_id <= 3;
+--------------+------------+-----------+
| name         | first_name | last_name |
+--------------+------------+-----------+
| Aarav Sharma | Aarav      | Sharma    |
| Priya Iyer   | Priya      | Iyer      |
| Rohan Mehta  | Rohan      | Mehta     |
+--------------+------------+-----------+
3 rows in set (0.00 sec)
name = 'Aarav Sharma'      LOCATE(' ', name) = 6
first_name = LEFT(name, 6 - 1)       = LEFT(name, 5) = 'Aarav'
last_name  = SUBSTRING(name, 6 + 1)  = from position 7 = 'Sharma'

It works, but there is a neater function for exactly this job.


SUBSTRING_INDEX: Split on a Delimiter

SUBSTRING_INDEX(str, delim, count) is a MySQL favourite:

  • count > 0: everything before the count-th delimiter, counting from the left.

  • count < 0: everything after the count-th delimiter, counting from the right.

SELECT name,
       SUBSTRING_INDEX(name, ' ', 1)  AS first_name,
       SUBSTRING_INDEX(name, ' ', -1) AS last_name
FROM employees
WHERE emp_id IN (4, 5, 6);
+--------------+------------+-----------+
| name         | first_name | last_name |
+--------------+------------+-----------+
| Sneha Kapoor | Sneha      | Kapoor    |
| Vikram Singh | Vikram     | Singh     |
| Ananya Das   | Ananya     | Das       |
+--------------+------------+-----------+
3 rows in set (0.00 sec)

The most-asked use: split an email into username and domain.

SELECT email,
       SUBSTRING_INDEX(email, '@', 1)  AS username,
       SUBSTRING_INDEX(email, '@', -1) AS domain
FROM employees
WHERE emp_id IN (1, 8);
+-------------------+----------+-------------+
| email             | username | domain      |
+-------------------+----------+-------------+
| aarav@company.com | aarav    | company.com |
| NULL              | NULL     | NULL        |
+-------------------+----------+-------------+
2 rows in set (0.00 sec)

With a count other than 1 you can take several pieces at once:

SELECT SUBSTRING_INDEX('www.cslearningportal.com', '.', 2)  AS two_parts,
       SUBSTRING_INDEX('www.cslearningportal.com', '.', -2) AS last_two;
+----------------------+----------------------+
| two_parts            | last_two             |
+----------------------+----------------------+
| www.cslearningportal | cslearningportal.com |
+----------------------+----------------------+
1 row in set (0.00 sec)

To get the middle piece, nest two calls: SUBSTRING_INDEX(SUBSTRING_INDEX(s, '.', 2), '.', -1) gives cslearningportal. If the delimiter is not present at all, SUBSTRING_INDEX returns the whole string, so a one-word name gives the same value as both first and last name.


Cleaning Up: TRIM, LTRIM, RTRIM

Extra spaces are the most common kind of dirty data, usually from forms or CSV imports. We wrap the results in brackets so the spaces are visible:

SELECT CONCAT('[', TRIM('   Pune  '), ']')  AS trimmed,
       CONCAT('[', LTRIM('   Pune  '), ']') AS ltrimmed,
       CONCAT('[', RTRIM('   Pune  '), ']') AS rtrimmed;
+---------+----------+-----------+
| trimmed | ltrimmed | rtrimmed  |
+---------+----------+-----------+
| [Pune]  | [Pune  ] | [   Pune] |
+---------+----------+-----------+
1 row in set (0.00 sec)

TRIM removes spaces from both ends, LTRIM from the left, RTRIM from the right. Spaces inside the string are not touched. The full TRIM syntax can remove any other character too:

SELECT TRIM(LEADING '0' FROM '000123')   AS no_lead_zero,
       TRIM(TRAILING '.' FROM 'Done...') AS no_dots,
       TRIM(BOTH '-' FROM '--SQL--')     AS no_dashes;
+--------------+---------+-----------+
| no_lead_zero | no_dots | no_dashes |
+--------------+---------+-----------+
| 123          | Done    | SQL       |
+--------------+---------+-----------+
1 row in set (0.00 sec)

Why does this matter so much? Because with MySQL 8's default collation, 'Pune ' = 'Pune' is false (trailing spaces count, as we saw in the WHERE Clause chapter). One stray space and a customer silently disappears from "all customers in Pune". Trim data when you store it, not on every query.


REPLACE: Find and Replace

REPLACE(str, from, to) replaces every occurrence of from with to:

SELECT email, REPLACE(email, '@company.com', '@company.in') AS new_email
FROM employees
WHERE emp_id IN (1, 2, 8);
+-------------------+------------------+
| email             | new_email        |
+-------------------+------------------+
| aarav@company.com | aarav@company.in |
| priya@company.com | priya@company.in |
| NULL              | NULL             |
+-------------------+------------------+
3 rows in set (0.00 sec)
SELECT REPLACE('Data Base', ' ', '')      AS no_space,
       REPLACE('banana', 'a', 'o')        AS all_replaced,
       REPLACE('Mumbai', 'mumbai', 'Bombay') AS case_sensitive;
+----------+--------------+----------------+
| no_space | all_replaced | case_sensitive |
+----------+--------------+----------------+
| DataBase | bonono       | Mumbai         |
+----------+--------------+----------------+
1 row in set (0.00 sec)

Notice the last column: REPLACE is case-sensitive, even though MySQL's = comparison is case-insensitive by default. 'mumbai' does not occur in 'Mumbai', so nothing changed. This surprises many people. (The regular-expression version, REGEXP_REPLACE('Mumbai', 'mumbai', 'Bombay'), which we meet later in this chapter, does follow the case-insensitive collation and returns Bombay.)

To actually change stored data, use REPLACE inside an UPDATE, for example UPDATE employees SET email = REPLACE(email, '@company.com', '@company.in');. (Do not confuse the REPLACE() function with MySQL's REPLACE INTO statement, which is an INSERT variant.)


Padding and Building Strings: LPAD, RPAD, REPEAT, REVERSE

LPAD(str, len, padstr) pads on the left until the string is len characters long; RPAD pads on the right. LPAD is how you make fixed-width codes with leading zeros:

SELECT order_id,
       CONCAT('ORD-', LPAD(order_id, 6, '0')) AS order_code,
       RPAD(status, 12, '.')                  AS padded
FROM orders
WHERE order_id IN (1001, 1005);
+----------+------------+--------------+
| order_id | order_code | padded       |
+----------+------------+--------------+
|     1001 | ORD-001001 | Delivered... |
|     1005 | ORD-001005 | Shipped..... |
+----------+------------+--------------+
2 rows in set (0.00 sec)

LPAD happily accepted the number order_id; MySQL converted it to a string. One gotcha: if the string is already longer than len, it is cut, not left alone. LPAD('Mumbai', 3, '*') returns Mum.

A few more small builders:

SELECT REVERSE('madam') AS r1, REVERSE('Pune') AS r2,
       REPEAT('ab', 3)  AS rep,
       CONCAT('[', SPACE(4), ']') AS spaces;
+-------+------+--------+--------+
| r1    | r2   | rep    | spaces |
+-------+------+--------+--------+
| madam | enuP | ababab | [    ] |
+-------+------+--------+--------+
1 row in set (0.00 sec)

"Is this word a palindrome?" in SQL is simply word = REVERSE(word).

Practical example: masking an email

Show only the first two letters of the username, hide the rest with stars, and keep the domain:

SELECT name, email,
       CONCAT(LEFT(email, 2),
              REPEAT('*', LOCATE('@', email) - 3),
              SUBSTRING(email, LOCATE('@', email))) AS masked_email
FROM employees
WHERE dept_id = 20;
+--------------+--------------------+--------------------+
| name         | email              | masked_email       |
+--------------+--------------------+--------------------+
| Sneha Kapoor | sneha@company.com  | sn***@company.com  |
| Vikram Singh | vikram@company.com | vi****@company.com |
| Ananya Das   | ananya@company.com | an****@company.com |
+--------------+--------------------+--------------------+
3 rows in set (0.00 sec)
email = 'sneha@company.com'      LOCATE('@', email) = 6
LEFT(email, 2)               = 'sn'
REPEAT('*', 6 - 3)           = '***'     (username has 5 chars, 2 shown, 3 hidden)
SUBSTRING(email, 6)          = '@company.com'

Practical example: usernames and employee codes

SELECT name,
       LOWER(CONCAT(SUBSTRING_INDEX(name, ' ', 1), '.',
                    SUBSTRING_INDEX(name, ' ', -1))) AS username,
       CONCAT('EMP', LPAD(emp_id, 4, '0'))          AS emp_code
FROM employees
WHERE dept_id = 10;
+--------------+--------------+----------+
| name         | username     | emp_code |
+--------------+--------------+----------+
| Aarav Sharma | aarav.sharma | EMP0001  |
| Priya Iyer   | priya.iyer   | EMP0002  |
| Rohan Mehta  | rohan.mehta  | EMP0003  |
+--------------+--------------+----------+
3 rows in set (0.00 sec)

FORMAT: Numbers as Readable Text (with Indian Grouping)

FORMAT(number, decimals [, locale]) rounds a number and adds thousands separators, returning a string. With the locale 'en_IN', MySQL uses the Indian lakh/crore grouping:

SELECT name, salary,
       FORMAT(salary, 2)          AS intl,
       FORMAT(salary, 0, 'en_IN') AS indian
FROM employees
WHERE emp_id IN (1, 8);
+--------------+-----------+------------+----------+
| name         | salary    | intl       | indian   |
+--------------+-----------+------------+----------+
| Aarav Sharma | 150000.00 | 150,000.00 | 1,50,000 |
| Meera Nair   |  48000.00 | 48,000.00  | 48,000   |
+--------------+-----------+------------+----------+
2 rows in set (0.00 sec)
SELECT FORMAT(1234567.891, 2)          AS f1,
       FORMAT(1234567.891, 2, 'en_IN') AS f2,
       FORMAT(1234567.891, 2, 'de_DE') AS f3;
+--------------+--------------+--------------+
| f1           | f2           | f3           |
+--------------+--------------+--------------+
| 1,234,567.89 | 12,34,567.89 | 1.234.567,89 |
+--------------+--------------+--------------+
1 row in set (0.00 sec)

Because the result is text (notice it is left-aligned), use FORMAT only for the final display. Sorting or doing arithmetic on formatted values gives wrong answers. Keep calculations on the original numeric column.


Comparing and Converting

STRCMP

STRCMP(a, b) returns -1 if a sorts before b, 0 if they are equal, and 1 if a sorts after b (like C's strcmp, but always exactly -1/0/1):

SELECT STRCMP('apple', 'banana') AS s1,
       STRCMP('banana', 'apple') AS s2,
       STRCMP('Apple', 'apple')  AS s3;
+----+----+----+
| s1 | s2 | s3 |
+----+----+----+
| -1 |  1 |  0 |
+----+----+----+
1 row in set (0.00 sec)

'Apple' and 'apple' are equal because STRCMP follows the collation, and the default collation is case-insensitive.

ASCII and CHAR

SELECT ASCII('A') AS a, ASCII('a') AS lower_a, CHAR(72, 105 USING utf8mb4) AS ch;
+----+---------+------+
| a  | lower_a | ch   |
+----+---------+------+
| 65 |      97 | Hi   |
+----+---------+------+
1 row in set (0.00 sec)

ASCII gives the code of the first character; CHAR builds a string from codes.

Strings vs numbers

MySQL converts between strings and numbers automatically, depending on the operator:

SELECT '5' + 3       AS plus,
       CONCAT(5, 3)  AS concat_num,
       '10' < '9'    AS string_compare,
       10 < 9        AS number_compare;
+------+------------+----------------+----------------+
| plus | concat_num | string_compare | number_compare |
+------+------------+----------------+----------------+
|    8 | 53         |              1 |              0 |
+------+------------+----------------+----------------+
1 row in set (0.00 sec)

+ is arithmetic, so '5' became the number 5. CONCAT is a string function, so 5 and 3 became text. And '10' < '9' is TRUE (1) because strings compare character by character, and '1' comes before '9'. This is why numbers stored in VARCHAR columns sort "wrongly" (1, 10, 2, 20 ...): store numbers in numeric types (SQL Data Types chapter).


Regular Expressions (MySQL 8)

When LIKE's % and _ are not enough, MySQL 8 offers regular-expression functions (based on the ICU library):

Function

Returns

REGEXP_LIKE(str, pattern) (or str REGEXP pattern)

1 if the pattern matches anywhere, else 0

REGEXP_REPLACE(str, pattern, repl)

String with every match replaced

REGEXP_SUBSTR(str, pattern)

The first matching substring

REGEXP_INSTR(str, pattern)

Position of the first match

Employees whose email username is exactly 5 lowercase letters:

SELECT name, email
FROM employees
WHERE REGEXP_LIKE(email, '^[a-z]{5}@');
+--------------+-------------------+
| name         | email             |
+--------------+-------------------+
| Aarav Sharma | aarav@company.com |
| Priya Iyer   | priya@company.com |
| Rohan Mehta  | rohan@company.com |
| Sneha Kapoor | sneha@company.com |
| Karan Patel  | karan@company.com |
| Arjun Reddy  | arjun@company.com |
+--------------+-------------------+
6 rows in set (0.01 sec)
SELECT REGEXP_REPLACE('Call 98765-43210 now', '[0-9]', '#') AS masked,
       REGEXP_SUBSTR('Order ORD-2024-0099 shipped', '[0-9]{4}-[0-9]{4}') AS code_part;
+----------------------+-----------+
| masked               | code_part |
+----------------------+-----------+
| Call #####-##### now | 2024-0099 |
+----------------------+-----------+
1 row in set (0.00 sec)

Regex is powerful but cannot use an index, so it scans every row. Prefer simple LIKE 'prefix%' when it is enough.


GROUP_CONCAT: Joining Values Across Rows

All functions so far work on one row at a time. GROUP_CONCAT is an aggregate string function: it joins values from many rows of a group into one string (other databases call this STRING_AGG or LISTAGG):

SELECT dept_id,
       GROUP_CONCAT(SUBSTRING_INDEX(name, ' ', 1)
                    ORDER BY name SEPARATOR ', ') AS team
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id;
+---------+-----------------------+
| dept_id | team                  |
+---------+-----------------------+
|      10 | Aarav, Priya, Rohan   |
|      20 | Ananya, Sneha, Vikram |
|      30 | Karan, Meera          |
|      40 | Arjun                 |
+---------+-----------------------+
4 rows in set (0.00 sec)

You can control the order inside the list (ORDER BY), the separator (default is a comma without a space), and remove duplicates with GROUP_CONCAT(DISTINCT ...). NULL values are skipped. Watch out: the result is cut off at group_concat_max_len characters (1024 by default) with only a warning, so very long lists get silently truncated unless you raise that setting.


Putting It Together: Cleaning Messy Sign-up Data

Let us clean some realistic dirty data. This demo used a small scratch table created in a copy of the sample database:

CREATE TABLE signups (id INT PRIMARY KEY, raw_name VARCHAR(50), raw_phone VARCHAR(20));
INSERT INTO signups VALUES
 (1, '  rahul VERMA ', '+91 98765-43210'),
 (2, 'KAVYA menon',    '098450 12345'),
 (3, ' neha   agarwal', '(022) 2345 6789');

SELECT id, CONCAT('[', raw_name, ']') AS raw_name,
       CONCAT('[', TRIM(raw_name), ']') AS trimmed
FROM signups;
+----+-------------------+------------------+
| id | raw_name          | trimmed          |
+----+-------------------+------------------+
|  1 | [  rahul VERMA ]  | [rahul VERMA]    |
|  2 | [KAVYA menon]     | [KAVYA menon]    |
|  3 | [ neha   agarwal] | [neha   agarwal] |
+----+-------------------+------------------+
3 rows in set (0.00 sec)

Now build a "Title Case" name (first letter upper, rest lower, for each of the two words) and keep only the digits of the phone number:

SELECT id,
       CONCAT(
         UPPER(LEFT(SUBSTRING_INDEX(TRIM(raw_name), ' ', 1), 1)),
         LOWER(SUBSTRING(SUBSTRING_INDEX(TRIM(raw_name), ' ', 1), 2)),
         ' ',
         UPPER(LEFT(SUBSTRING_INDEX(TRIM(raw_name), ' ', -1), 1)),
         LOWER(SUBSTRING(SUBSTRING_INDEX(TRIM(raw_name), ' ', -1), 2))
       ) AS clean_name,
       REGEXP_REPLACE(raw_phone, '[^0-9]', '')            AS digits_only,
       RIGHT(REGEXP_REPLACE(raw_phone, '[^0-9]', ''), 10) AS last10
FROM signups;
+----+--------------+--------------+------------+
| id | clean_name   | digits_only  | last10     |
+----+--------------+--------------+------------+
|  1 | Rahul Verma  | 919876543210 | 9876543210 |
|  2 | Kavya Menon  | 09845012345  | 9845012345 |
|  3 | Neha Agarwal | 02223456789  | 2223456789 |
+----+--------------+--------------+------------+
3 rows in set (0.00 sec)

Step by step for row 1: TRIM gives "rahul VERMA"; SUBSTRING_INDEX(..., ' ', 1) gives "rahul" and (..., ' ', -1) gives "VERMA"; for each word, UPPER(LEFT(w, 1)) + LOWER(SUBSTRING(w, 2)) gives "Rahul" and "Verma". For the phone, [^0-9] means "any character that is not a digit", so REGEXP_REPLACE deletes the +, spaces, hyphens and brackets, and RIGHT(..., 10) drops the country code or leading 0 to give the standard 10-digit form.

Row 3 had three spaces between the words; SUBSTRING_INDEX(..., ' ', -1) still found "agarwal" because it takes the text after the last space. To collapse repeated inner spaces in general, use REGEXP_REPLACE(TRIM(s), ' +', ' '), which turns ' neha agarwal' into 'neha agarwal'. In a real system you would run these expressions once in an UPDATE (or better, clean the data in the application before inserting).


Performance: Functions on Columns in WHERE

These two queries return the same 3 rows (Aarav, Ananya, Arjun):

SELECT name FROM employees WHERE LEFT(name, 1) = 'A';   -- function on the column
SELECT name FROM employees WHERE name LIKE 'A%';        -- column left alone

But on a large table with an index on name, only the second one can use the index. Once a column is wrapped in a function, MySQL must compute the function for every row before comparing, so it cannot jump into the sorted index. (This is the "sargable" idea from the WHERE Clause chapter, explained fully in the Indexes chapter.) Prefer LIKE 'prefix%' over LEFT()/SUBSTRING() in filters, and avoid WHERE UPPER(col) = ... since the collation is already case-insensitive.


Common Mistakes

Mistake

What happens

Fix

Using + or || to join strings

+ does arithmetic ('a' + 'b' = 0); || is logical OR

CONCAT()

CONCAT with a nullable column

Whole result NULL

COALESCE the column, or CONCAT_WS

Starting positions at 0

SUBSTRING(s, 0) returns ''

Positions start at 1

LENGTH for character limits

Counts bytes; wrong for ₹ or Hindi text

CHAR_LENGTH

Expecting REPLACE to ignore case

No replacement happens

Match the exact case, or use REGEXP_REPLACE, which follows the (case-insensitive) collation

LPAD with a too-small length

String is truncated

Choose a length ≥ the longest value

Sorting/calculating on FORMAT() output

Text order, wrong maths

Format only for display

Functions on indexed columns in WHERE

Full table scan

Rewrite as LIKE 'x%' or a range

Long GROUP_CONCAT lists

Silently cut at 1024 chars

Raise group_concat_max_len


Interview Questions

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

LENGTH returns the size in bytes; CHAR_LENGTH returns the number of characters. They differ for multi-byte characters in utf8mb4: LENGTH('₹500') is 6 but CHAR_LENGTH('₹500') is 4.

What is the difference between CONCAT and CONCAT_WS?

CONCAT joins its arguments and returns NULL if any argument is NULL. CONCAT_WS takes a separator as its first argument, places it between values, and skips NULL values (it is NULL only if the separator is NULL).

Extract the domain from an email address.

SUBSTRING_INDEX(email, '@', -1), or SUBSTRING(email, LOCATE('@', email) + 1).

Split a full name into first and last name.

SUBSTRING_INDEX(name, ' ', 1) for the first name and SUBSTRING_INDEX(name, ' ', -1) for the last name.

Are string positions 0-based or 1-based in SQL? What does LOCATE return when not found?

1-based. LOCATE/INSTR/POSITION return 0 when the substring is not found.

How do you remove leading zeros from a string?

TRIM(LEADING '0' FROM s). (If the value is purely numeric, CAST(s AS UNSIGNED) also works.)

How do you display numbers in Indian format (lakhs, crores)?

FORMAT(n, 2, 'en_IN'), e.g. 1234567.891 becomes 12,34,567.89.

How do you check whether a string is a palindrome in SQL?

WHERE s = REVERSE(s). With the default case-insensitive collation, 'Madam' also counts as a palindrome.

How do you list all employee names of each department in one row?

SELECT dept_id, GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') FROM employees GROUP BY dept_id;

Why is WHERE UPPER(name) = 'AARAV SHARMA' a bad idea in MySQL?

It is unnecessary, because the default collation is case-insensitive, and harmful, because wrapping the column in a function prevents index use.


Summary

  • String positions start at 1; functions return new values and do not modify the table; NULL input usually gives NULL output.

  • CONCAT joins strings (NULL-sensitive); CONCAT_WS adds a separator and skips NULLs; GROUP_CONCAT joins across rows.

  • CHAR_LENGTH counts characters, LENGTH counts bytes.

  • SUBSTRING, LEFT, RIGHT extract by position; LOCATE/INSTR/POSITION find positions; SUBSTRING_INDEX splits on a delimiter.

  • TRIM, REPLACE (case-sensitive), LPAD/RPAD, REVERSE, REPEAT clean and build strings.

  • FORMAT(n, d, 'en_IN') gives Indian number formatting, for display only.

  • MySQL 8 has REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR for pattern work.

  • Avoid wrapping indexed columns in functions inside WHERE.

Text is one half of everyday data; dates are the other. In the next chapter, Date & Time Functions, we will extract years and months, add intervals, and calculate differences between dates.