MySQL String Functions

MySQL has many functions to work with text. You can use them in SELECT, WHERE and UPDATE.

SELECT
  CONCAT('Hello', ' ', 'World') AS joined,
  UPPER('hello') AS upper_text,
  LOWER('HELLO') AS lower_text,
  LENGTH('MySQL') AS length_text,
  SUBSTRING('Hello World', 1, 5) AS part,
  LEFT('Robin', 3) AS first_three,
  REPLACE('Hello World', 'World', 'SQL') AS replaced,
  TRIM('   hi   ') AS trimmed;

Function	Use
CONCAT(a, b, ...)	Joins texts
CONCAT_WS(sep, a, b)	Joins with a separator
UPPER(), LOWER()	Changes the case
LENGTH(), CHAR_LENGTH()	Length in bytes and in characters
SUBSTRING(text, start, length)	A part of the text. Start counts from 1
LEFT(), RIGHT()	First or last characters
REPLACE(text, old, new)	Replaces text
TRIM()	Removes spaces at both ends
LOCATE(find, text)	Position of a text

---

Use them on a table

SELECT CONCAT(name, ' (', city, ')') AS student
FROM students
WHERE LENGTH(name) = 5
ORDER BY id;

Only Robin and Maria have names with 5 letters. Read the interview question first four characters of a name.

---

Summary

• CONCAT, UPPER, LOWER, SUBSTRING, REPLACE and TRIM are the most used.
• Next: Date functions.

• Previous Page
• Next Page

---

Exercises & Assignments
No Content Found.

Interview Questions & Answers
No Content Found.