MySQL String Functions

MySQL provides a rich set of built-in string functions to manipulate and format text data stored in columns. These functions allow changing case, extracting parts of strings, measuring length, replacing content, and much more — all within SQL queries.

Commonly Used String Functions

Function	Description
UPPER()	Converts text to uppercase
LOWER()	Converts text to lowercase
LENGTH()	Returns the number of bytes in a string
CHAR_LENGTH()	Returns the number of characters in a string
CONCAT()	Joins two or more strings together
CONCAT_WS()	Joins strings with a separator
SUBSTRING()	Extracts part of a string
LEFT()	Returns characters from the left side
RIGHT()	Returns characters from the right side
TRIM()	Removes leading and trailing spaces
REPLACE()	Replaces occurrences of a substring
INSTR()	Returns the position of a substring
REVERSE()	Reverses a string
LPAD() / RPAD()	Pads a string to a specified length

UPPER() and LOWER()

SELECT UPPER('hello world');    -- HELLO WORLD
SELECT LOWER('MYSQL Tutorial'); -- mysql tutorial

LENGTH() and CHAR_LENGTH()

SELECT LENGTH('MySQL');       -- 5
SELECT CHAR_LENGTH('MySQL');  -- 5

CONCAT() and CONCAT_WS()

SELECT CONCAT('Ravi', ' ', 'Kumar');       -- Ravi Kumar
SELECT CONCAT_WS(', ', 'Delhi', 'India'); -- Delhi, India

Using CONCAT in a Query

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

SUBSTRING()

SELECT SUBSTRING('MySQL Tutorial', 1, 5);   -- MySQL
SELECT SUBSTRING('MySQL Tutorial', 7);      -- Tutorial

The first number is the starting position (1-based), the second is the length. If no length is given, it returns to the end.

LEFT() and RIGHT()

SELECT LEFT('Bangalore', 4);   -- Bang
SELECT RIGHT('Bangalore', 4);  -- lore

TRIM()

SELECT TRIM('   Hello   ');          -- Hello
SELECT LTRIM('   Hello   ');         -- Hello    (removes left spaces only)
SELECT RTRIM('   Hello   ');         --    Hello (removes right spaces only)

REPLACE()

SELECT REPLACE('Good Morning', 'Morning', 'Evening'); -- Good Evening

Using REPLACE in a Table

UPDATE products SET product_name = REPLACE(product_name, 'Old', 'New');

INSTR()

SELECT INSTR('MySQL Tutorial', 'Tutorial'); -- 7

Returns the starting position of the first occurrence. Returns 0 if not found.

REVERSE()

SELECT REVERSE('India'); --aidnI

LPAD() and RPAD()

SELECT LPAD('42', 5, '0');  -- 00042
SELECT RPAD('Hi', 5, '.');  -- Hi...

Practical Example: Formatting Employee Names

SELECT
    emp_id,
    UPPER(emp_name) AS Name_Upper,
    CHAR_LENGTH(emp_name) AS Name_Length,
    LEFT(emp_name, 5) AS Short_Name
FROM employees;

Key Points

String functions in MySQL manipulate text data without modifying the stored values.
CONCAT() joins strings; CONCAT_WS() adds a separator between values.
SUBSTRING(), LEFT(), and RIGHT() extract parts of a string.
TRIM() removes unnecessary spaces from both ends of a string.
REPLACE() substitutes part of a string with a new value.

Previous lessons

Back to courses

Next lessons