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

LENGTH() returns the physical storage length of the string in bytes. CHAR_LENGTH() returns the actual count of characters. For multi-byte UTF-8 characters (like Hindi, Chinese, or emojis), CHAR_LENGTH() accurately counts characters whereas LENGTH() counts raw bytes.

How does CONCAT_WS() work in MySQL?

CONCAT_WS() stands for 'Concatenate With Separator'. The first argument is the separator (e.g., CONCAT_WS(' ', first_name, last_name)), and it automatically skips any NULL values without turning the entire result into NULL.

Are string indexing offsets in SQL 0-based or 1-based?

SQL is 1-based! SUBSTRING('Veewom', 1, 3) starts at index 1 and returns 'Vee'.

SQL String Functions: Text Formatting, Concatenation & Parsing

Relational databases process massive volumes of textual data: usernames, addresses, emails, and articles. Modern SQL dialects provide a comprehensive suite of built-in String Functions to format names, extract substrings, remove whitespace, and clean data before presentation.

Key SQL String Functions Reference

Function & Syntax	Description	Practical Example Output
CONCAT(s1, s2, ...)	Concatenates two or more strings together.	`CONCAT('Aman', ' ', 'Verma')` → 'Aman Verma'
CONCAT_WS(sep, s1, ...)	Concatenates with separator, automatically ignoring NULLs.	`CONCAT_WS(', ', 'Delhi', 'India')` → 'Delhi, India'
SUBSTRING(str, pos, len)	Extracts a substring starting at 1-based position for `len` characters.	`SUBSTRING('Veewom', 1, 4)` → 'Veew'
UPPER(str) / LOWER(str)	Converts text to uppercase or lowercase.	`UPPER('bca')` → 'BCA'
TRIM(str)	Strips leading and trailing whitespace.	`TRIM(' hello ')` → 'hello'
REPLACE(str, from, to)	Replaces all occurrences of a substring.	`REPLACE('2026/11/06', '/', '-')` → '2026-11-06'
CHAR_LENGTH(str)	Returns character count of the text.	`CHAR_LENGTH('Veewom')` → 6

Hands-on Query Examples

-- Format clean student full names and standardized uppercase emails
SELECT 
    student_id,
    CONCAT(UPPER(last_name), ', ', first_name) AS formal_name,
    LOWER(email) AS standardized_email,
    SUBSTRING(enrollment_date, 1, 4) AS enrollment_year
FROM students;

Next, let us explore mathematical calculations using SQL Math & Numeric Functions!