Nine functions that take a piece of text and hand back something made from it. None of them changes the table — like an alias, they affect only what the resultset shows. Several come in two spellings, and both are examinable.
📝Who this page is for
Text functions are in the Informatics Practices (065) Class 12 syllabus, Unit 2, and not in the Computer Science (083) one. CS students may skip this page.
1UPPER and LOWER
MySQL command line client
mysql> select upper('HelLO HOw aRe YOu?');
+-----------------------------+
| upper('HelLO HOw aRe YOu?') |
+-----------------------------+
| HELLO HOW ARE YOU? |
+-----------------------------+
1 row in set (0.00 sec)
mysql> select lower('HelLO HOw aRe YOu?');
+-----------------------------+
| lower('HelLO HOw aRe YOu?') |
+-----------------------------+
| hello how are you? |
+-----------------------------+
1 row in set (0.00 sec)
Every letter is converted; the spaces, digits and the question mark are left exactly as they were. Only alphabetic characters have a case to change.
🔑Two names for each
upper() is also ucase(), and lower() is also lcase(). They are the same function, and a question may use either name:
MySQL command line client
mysql> select ucase('lambdalab school');
+---------------------------+
| ucase('lambdalab school') |
+---------------------------+
| LAMBDALAB SCHOOL |
+---------------------------+
1 row in set (0.00 sec)
mysql> select lcase('LAMBDALAB SCHOOL');
+---------------------------+
| lcase('LAMBDALAB SCHOOL') |
+---------------------------+
| lambdalab school |
+---------------------------+
1 row in set (0.00 sec)
2LENGTH — how many characters
MySQL command line client
mysql> select length("good morning");
+------------------------+
| length("good morning") |
+------------------------+
| 12 |
+------------------------+
1 row in set (0.00 sec)
Twelve, not eleven — the space is a character. “good” is 4, “morning” is 7, and the space between them makes 12. Forgetting the space is the classic wrong answer.
3MID — a piece from the middle
mid(text, start, n) takes n characters beginning at position start:
MySQL command line client
mysql> select mid('Good Morning', 4, 5);
+---------------------------+
| mid('Good Morning', 4, 5) |
+---------------------------+
| d Mor |
+---------------------------+
1 row in set (0.00 sec)
⚠️SQL counts from 1, not from 0
Position 4 of Good Morning is the d, not the space. In Python a string starts at index 0; in SQL the first character is position 1. This single difference accounts for most wrong answers in this topic.
Ask for more characters than are left and you simply get what there is — no error:
MySQL command line client
mysql> select substr('Good Morning', 4, 50);
+-------------------------------+
| substr('Good Morning', 4, 50) |
+-------------------------------+
| d Morning |
+-------------------------------+
1 row in set (0.00 sec)
mid(), substring() and substr() are three names for one function. Any of them is correct.
4LEFT and RIGHT — from the ends
MySQL command line client
mysql> select left("hello world", 4);
+------------------------+
| left("hello world", 4) |
+------------------------+
| hell |
+------------------------+
1 row in set (0.00 sec)
mysql> select right("hello world", 5);
+-------------------------+
| right("hello world", 5) |
+-------------------------+
| world |
+-------------------------+
1 row in set (0.00 sec)
Simpler than mid() because there is no starting position to work out — you are counting in from one end or the other.
5INSTR — where does it occur?
MySQL command line client
mysql> select instr("hello world", "w");
+---------------------------+
| instr("hello world", "w") |
+---------------------------+
| 7 |
+---------------------------+
1 row in set (0.00 sec)
mysql> select instr("together", "get");
+--------------------------+
| instr("together", "get") |
+--------------------------+
| 3 |
+--------------------------+
1 row in set (0.00 sec)
The answer is the position where the search text starts, counting from 1. In together, the letters get begin at the third character. If the text is not found at all, instr() returns 0 — and 0 is not a valid position, which is how you can tell.
6LTRIM, RTRIM and TRIM
These remove spaces from the ends. To see what they do, the results below are wrapped in | characters using concat(), because otherwise the removed spaces are invisible:
Data typed by people arrives with stray spaces, and " Singtam" will not match "Singtam" in a where. None of the three touches spaces inside the text — trim("New Delhi") keeps its middle space.
7All of them at a glance
Text functions
Function
Also written
Does
Example → result
upper(s)
ucase(s)
Converts to capitals
upper('kv') → KV
lower(s)
lcase(s)
Converts to small letters
lower('KV') → kv
length(s)
—
Counts characters, spaces included
length('good morning') → 12
mid(s,p,n)
substring, substr
n characters from position p
mid('Good Morning',4,5) → d Mor
left(s,n)
—
First n characters
left('hello world',4) → hell
right(s,n)
—
Last n characters
right('hello world',5) → world
instr(s,t)
—
Position where t starts, or 0
instr('together','get') → 3
ltrim(s)
—
Removes leading spaces
ltrim(' KV') → KV
rtrim(s) / trim(s)
—
Removes trailing / both
trim(' KV ') → KV
8Recap
Positions start at 1
Not 0 as in Python. The commonest error in this topic.
length counts spaces
'good morning' is 12, not 11.
Two names each
upper/ucase, lower/lcase, mid/substring/substr.
instr returns 0
When the text is not found — 0 is never a real position.
✏️ Quick Check
What does select length('good morning'); return?
✏️ Quick Check
What does select mid('Good Morning', 4, 5); return?
✏️ Quick Check
What does select instr('together', 'get'); return?
✏️ Quick Check
Which function removes spaces from the end of a string only?