String Functions

Length and case

SELECT LENGTH('বাংলা');        -- 15 (bytes)
SELECT CHAR_LENGTH('বাংলা');   -- 5  (characters) ← use this for Unicode
SELECT UPPER('mysql'), LOWER('MySQL');

For Bengali, Arabic or emoji text always use CHAR_LENGTH, never LENGTH.

Joining and splitting

SELECT CONCAT(name_en, ' - ', department) FROM students;
SELECT CONCAT_WS(', ', name_en, email, department) FROM students;  -- skips NULLs
SELECT SUBSTRING('MySQL Tutorial', 1, 5);      -- 'MySQL'
SELECT SUBSTRING('MySQL Tutorial', -8);        -- 'Tutorial'
SELECT LEFT('Bangladesh', 5), RIGHT('Bangladesh', 4);
SELECT SUBSTRING_INDEX('rahim@example.com', '@', -1);  -- 'example.com'

CONCAT returns NULL if any argument is NULL; CONCAT_WS simply skips them.

Trimming and padding

SELECT TRIM('  hello  '), LTRIM('  hi'), RTRIM('hi  ');
SELECT TRIM(BOTH 'x' FROM 'xxhelloxx');   -- 'hello'
SELECT LPAD('7', 3, '0');                 -- '007'
SELECT RPAD('CSE', 6, '-');               -- 'CSE---'

Searching and replacing

SELECT LOCATE('SQL', 'MySQL');          -- 3  (0 if not found)
SELECT INSTR('MySQL', 'S');             -- 3
SELECT REPLACE('2024-01-15', '-', '/'); -- '2024/01/15'
SELECT REVERSE('abc'), REPEAT('ab', 3);

Formatting and comparison

SELECT FORMAT(1234567.891, 2);          -- '1,234,567.89'
SELECT STRCMP('a', 'b');                -- -1
SELECT ELT(2, 'one', 'two', 'three');   -- 'two'
SELECT FIELD('EEE', 'CSE', 'EEE');      -- 2

Practical examples

-- capitalise the first letter of each name
SELECT CONCAT(UPPER(LEFT(name_en,1)), LOWER(SUBSTRING(name_en,2))) FROM students;

-- mask an email for display
SELECT CONCAT(LEFT(email,2), '****@', SUBSTRING_INDEX(email,'@',-1)) FROM students;

-- normalise messy input before comparing
SELECT * FROM students WHERE LOWER(TRIM(email)) = 'rahim@example.com';

Wrapping a column in a function inside WHERE disables its index. If you need case-insensitive search, rely on a _ci collation (the default) rather than calling LOWER().