String functions
40 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)A string function takes text and gives back new text or a number: the text in capitals, its length, a piece of it, or two texts joined. Like every expression in SELECT, it changes only the result, never the stored data.
UPPER, LOWER, LENGTH, CHAR_LENGTH and TRIM
UPPER(s) and LOWER(s) change the case of text and TRIM(s) removes spaces at both ends. CHAR_LENGTH(s) counts characters, while LENGTH(s) counts bytes, the units the text takes in storage.
Names and emails typed by people are messy: ' asha rao ', 'ASHA.RAO@GMAIL.COM'. The first example cleans a sign-up list with TRIM, UPPER and LOWER; the second compares characters with bytes.
Example 1 · Cleaning a fest sign-up form
DROP TABLE IF EXISTS fest_signups;CREATE TABLE fest_signups ( signup_id INT PRIMARY KEY, full_name VARCHAR(40) NOT NULL, email VARCHAR(60) NOT NULL, college VARCHAR(30) NOT NULL);INSERT INTO fest_signups VALUES (1, ' asha rao ', 'ASHA.RAO@GMAIL.COM', ' pune'), (2, 'RAVI KUMAR', 'ravi.k@yahoo.in ', 'Chennai '), (3, 'meena', ' Meena@Outlook.com', 'chennai');
SELECT signup_id, CHAR_LENGTH(full_name) AS raw_length, CHAR_LENGTH(TRIM(full_name)) AS trimmed_length, UPPER(TRIM(full_name)) AS name_caps, LOWER(TRIM(email)) AS clean_email, TRIM(college) AS collegeFROM fest_signupsORDER BY signup_id;+-----------+------------+----------------+------------+--------------------+---------+ | signup_id | raw_length | trimmed_length | name_caps | clean_email | college | +-----------+------------+----------------+------------+--------------------+---------+ | 1 | 11 | 8 | ASHA RAO | asha.rao@gmail.com | pune | | 2 | 10 | 10 | RAVI KUMAR | ravi.k@yahoo.in | Chennai | | 3 | 5 | 5 | MEENA | meena@outlook.com | chennai | +-----------+------------+----------------+------------+--------------------+---------+
What this code does
' asha rao 'has 11 characters, but only 8 afterTRIMremoves the two spaces in front and one at the end. Functions can be nested: TRIM runs first, then UPPER or LOWER.LOWER(TRIM(email))gives every email one standard form, so' Meena@Outlook.com'andmeena@outlook.comwould now match. The table still holds the messy values.
Example 2 · LENGTH counts bytes, CHAR_LENGTH counts characters
SELECT CHAR_LENGTH('Pune') AS pune_chars, LENGTH('Pune') AS pune_bytes, CHAR_LENGTH('₹500') AS rupee_chars, LENGTH('₹500') AS rupee_bytes, CHAR_LENGTH('नमस्ते') AS hindi_chars, LENGTH('नमस्ते') AS hindi_bytes;+------------+------------+-------------+-------------+-------------+-------------+ | pune_chars | pune_bytes | rupee_chars | rupee_bytes | hindi_chars | hindi_bytes | +------------+------------+-------------+-------------+-------------+-------------+ | 4 | 4 | 4 | 6 | 6 | 18 | +------------+------------+-------------+-------------+-------------+-------------+
What this code does
- English letters take 1 byte each, so both functions give 4 for
Pune. The rupee sign takes 3 bytes in utf8mb4, so₹500is 4 characters but 6 bytes. - The Hindi word is 6 characters (letters and vowel signs) and 18 bytes. Use
CHAR_LENGTHfor limits people understand, such as "up to 40 characters".
LOWER(TRIM(email)) turns every way of typing an email into one standard form. For English letters one character is one byte; with utf8mb4 a character such as ₹ takes 3 bytes, so LENGTH('₹500') is 6 but CHAR_LENGTH('₹500') is 4.
WHERE city = 'pune' already ignores case, so UPPER is not needed to compare. Spaces are different: ' pune' does not equal 'pune', so TRIM is needed.CONCAT and CONCAT_WS
CONCAT(a, b, ...) joins its arguments into one text. CONCAT_WS(separator, a, b, ...) joins them with a separator between each pair, and skips any NULL argument.
A screen shows "Name - Phone" lines. The query below builds them three ways, and Meena Iyer, who has no phone, shows what a NULL part does to CONCAT.
Example 3 · CONCAT loses the line on NULL; CONCAT_WS does not
SELECT customer_id, CONCAT(name, ' - ', phone) AS with_concat, CONCAT(name, ' - ', IFNULL(phone, 'no phone')) AS with_ifnull, CONCAT_WS(' | ', name, phone, city) AS with_wsFROM customersWHERE customer_id BETWEEN 2 AND 4ORDER BY customer_id;+-------------+--------------------------+--------------------------+-----------------------------------+ | customer_id | with_concat | with_ifnull | with_ws | +-------------+--------------------------+--------------------------+-----------------------------------+ | 2 | Ravi Kumar - 9876500002 | Ravi Kumar - 9876500002 | Ravi Kumar | 9876500002 | Chennai | | 3 | NULL | Meena Iyer - no phone | Meena Iyer | Chennai | | 4 | Kabir Singh - 9876500004 | Kabir Singh - 9876500004 | Kabir Singh | 9876500004 | Delhi | +-------------+--------------------------+--------------------------+-----------------------------------+
What this code does
- Meena Iyer (customer 3) has no phone, so
CONCATreturns NULL for her whole line: one NULL part makes the result NULL. IFNULLputs a placeholder in, andCONCAT_WSsimply skips the NULL and its separator, givingMeena Iyer | Chennai.
If any argument of CONCAT is NULL, the whole result is NULL, so a customer without a phone loses the entire line. CONCAT_WS skips NULL arguments, and CONCAT with IFNULL(phone, 'no phone') keeps a placeholder.
SUBSTRING, LOCATE and REPLACE
SUBSTRING(s, start, length) cuts out part of a text; positions start at 1. LOCATE(find, s) gives the position of find inside s, or 0, and REPLACE(s, from, to) swaps every copy of from for to.
To take an email apart you need the position of the @. The first query splits each email into username and domain; the second masks phone numbers and replaces a domain.
Example 4 · Taking an email apart
SELECT email, LOCATE('@', email) AS at_position, SUBSTRING(email, 1, LOCATE('@', email) - 1) AS username, SUBSTRING_INDEX(email, '@', -1) AS domain, LEFT(name, 1) AS initialFROM customersWHERE city = 'Delhi'ORDER BY customer_id;+-------------------------+-------------+-------------+-------------+---------+ | email | at_position | username | domain | initial | +-------------------------+-------------+-------------+-------------+---------+ | kabir.singh@example.com | 12 | kabir.singh | example.com | K | | rahul.verma@example.com | 12 | rahul.verma | example.com | R | +-------------------------+-------------+-------------+-------------+---------+
What this code does
LOCATE('@', email)finds the@. TakingLOCATE - 1characters from position 1 gives everything before it, the username.SUBSTRING_INDEX(email, '@', -1)returns the part after the last@in one call.LEFT(name, 1)is the first letter, handy for an avatar circle in an app.
Example 5 · Masking phone numbers and replacing a domain
SELECT name, CONCAT('XXXXXX', RIGHT(phone, 4)) AS masked_phone, REPLACE(email, '@example.com', '@shoplite.in') AS new_emailFROM customersWHERE customer_id <= 4ORDER BY customer_id;+-------------+--------------+-------------------------+ | name | masked_phone | new_email | +-------------+--------------+-------------------------+ | Asha Rao | XXXXXX0001 | asha.rao@shoplite.in | | Ravi Kumar | XXXXXX0002 | ravi.kumar@shoplite.in | | Meena Iyer | NULL | meena.iyer@shoplite.in | | Kabir Singh | XXXXXX0004 | kabir.singh@shoplite.in | +-------------+--------------+-------------------------+
What this code does
RIGHT(phone, 4)keeps the last four digits, and CONCAT puts six X's in front, so support staff can confirm a number without seeing it. Meena's NULL phone gives NULL, as CONCAT always does.REPLACEswaps every@example.comfor@shoplite.in. This only shows the new addresses; changing the table would need anUPDATE ... SET email = REPLACE(...).
LEFT(s, n) and RIGHT(s, n) are short forms for the first or last n characters, and SUBSTRING_INDEX(s, '@', -1) returns everything after the last @.
SUBSTRING('Ravi Kumar', 6)→Kumar;SUBSTRING('Ravi Kumar', 1, 4)→Ravi.LOCATE('@', 'asha.rao@example.com')→ 9.REPLACE('98765-00001', '-', '')→9876500001.- Masking:
CONCAT('XXXXXX', RIGHT(phone, 4))shows only the last four digits, as banking and UPI apps do.
Predict the output
What does it return?
Read the code and pick what it prints.
Round 1 of 3
Positions start at 1.
LPAD and formatting for reports
LPAD(s, n, fill) pads text on the left with fill until it is n characters long; RPAD pads on the right. FORMAT(number, decimals, locale) writes a number with thousands separators.
Order numbers look better at a fixed width, and rupee amounts with Indian commas. The queries below turn order 7 into SL-00007 and 719960 into 7,19,960.00.
Example 6 · Order codes and a rupee report
SELECT CONCAT('SL-', LPAD(order_id, 5, '0')) AS order_no, statusFROM ordersWHERE order_id IN (7, 12)ORDER BY order_id;
SELECT RPAD(name, 22, '.') AS product, FORMAT(price * stock, 2, 'en_IN') AS stock_valueFROM productsWHERE category_id IN (2, 3)ORDER BY product_id;+----------+-----------+ | order_no | status | +----------+-----------+ | SL-00007 | SHIPPED | | SL-00012 | DELIVERED | +----------+-----------+ +------------------------+-------------+ | product | stock_value | +------------------------+-------------+ | Redmi Note 13......... | 7,19,960.00 | | Samsung Galaxy M35.... | 4,99,975.00 | | iPhone 15............. | 5,59,200.00 | | Lenovo IdeaPad Slim 3. | 5,15,880.00 | | HP Victus Gaming...... | 3,24,950.00 | +------------------------+-------------+
What this code does
LPAD(order_id, 5, '0')pads 7 to00007and 12 to00012, so every order number has the same width.RPADfills each name with dots to 22 characters, andFORMAT(..., 2, 'en_IN')writes7,19,960.00in Indian grouping. Both columns are text now, so the client aligns them to the left.
If the text is already longer than n, LPAD cuts it to n characters. These functions return text: right for the final report, but keep the raw numbers for sorting and calculating, because '9,999' sorts after '10,000'.
Next step
Check your understanding
11 quick questions