MySQL Built-in Functions I — String Functions

MySQL provides many ready-made built-in functions that perform common operations on values. They are grouped by the kind of data they work on: string (text) functions, mathematical (numeric) functions and date-time functions. This section covers the common string functions; the next two cover the others. A string function takes one or more text values and returns a text (or number) result. The most useful ones for a Std-12 student are:

• LENGTH(str) — the number of characters in the string. LENGTH('MySQL') is 5.
• UPPER(str) (also UCASE) — converts the text to capital letters. UPPER('das') is 'DAS'.
• LOWER(str) (also LCASE) — converts to small letters. LOWER('DAS') is 'das'.
• CONCAT(a, b, ...) — joins two or more strings into one. CONCAT('Aarti', ' ', 'Behera') is 'Aarti Behera'.
• SUBSTR(str, start, length) (also SUBSTRING / MID) — takes out a piece of the string, beginning at position start (counting from 1) for length characters. SUBSTR('Behera', 1, 3) is 'Beh'.
• LEFT(str, n) and RIGHT(str, n) — the first n and the last n characters. LEFT('MySQL', 2) is 'My'; RIGHT('MySQL', 3) is 'SQL'.
• TRIM(str) — removes spaces from both ends of the string (LTRIM and RTRIM remove them from the left or right only).
• INSTR(str, sub) — the position at which the substring sub first appears in str (0 if it does not occur). INSTR('Behera', 'he') is 2.
• REPLACE(str, from, to) — replaces every occurrence of one piece of text with another. REPLACE('Sales', 'S', 'T') is 'Tales' (the leading S becomes T).

These functions are used inside a SELECT. For example, to show each employee's name in capitals together with the length of the name:

SELECT UPPER(Name) AS NameCaps, LENGTH(Name) AS Letters
FROM Employee;

To display a greeting for each employee by joining fixed text with the name: