🛢️ Unit 2 – Database Query using SQL · Lesson 02
SQL Text Functions
Character positions
Text functions work on strings. In SQL the first character is at position 1; a negative position counts from the right end.
UCASE / LCASE
SQL
SELECT UCASE('Informatics'), LCASE('PRACTICES'), UPPER('sql'), LOWER('MySQL');Output
+----------------------+--------------------+--------------+----------------+
| UCASE('Informatics') | LCASE('PRACTICES') | UPPER('sql') | LOWER('MySQL') |
+----------------------+--------------------+--------------+----------------+
| INFORMATICS | practices | SQL | mysql |
+----------------------+--------------------+--------------+----------------+LENGTH
Spaces are characters too, so 'Hello World' has 11 characters.
SQL
SELECT LENGTH('Informatics'), LENGTH('Hello World'), LENGTH('');Output
+-----------------------+-----------------------+------------+
| LENGTH('Informatics') | LENGTH('Hello World') | LENGTH('') |
+-----------------------+-----------------------+------------+
| 11 | 11 | 0 |
+-----------------------+-----------------------+------------+LEFT and RIGHT
SQL
SELECT LEFT('Informatics', 4), RIGHT('Informatics', 5);Output
+------------------------+-------------------------+
| LEFT('Informatics', 4) | RIGHT('Informatics', 5) |
+------------------------+-------------------------+
| Info | atics |
+------------------------+-------------------------+MID / SUBSTR / SUBSTRING
MID(str, pos, n) extracts n characters starting at pos. SUBSTR and SUBSTRING are the same function. Without n it takes everything till the end.
SQL
SELECT MID('Informatics', 3, 4), SUBSTRING('Informatics', 6), SUBSTR('Informatics', -3, 2);Output
+--------------------------+-----------------------------+------------------------------+
| MID('Informatics', 3, 4) | SUBSTRING('Informatics', 6) | SUBSTR('Informatics', -3, 2) |
+--------------------------+-----------------------------+------------------------------+
| form | matics | ic |
+--------------------------+-----------------------------+------------------------------+INSTR
SQL
SELECT INSTR('Informatics', 'mat'), INSTR('Informatics', 'z');Output
+-----------------------------+---------------------------+
| INSTR('Informatics', 'mat') | INSTR('Informatics', 'z') |
+-----------------------------+---------------------------+
| 6 | 0 |
+-----------------------------+---------------------------+LTRIM, RTRIM, TRIM
Brackets are added with CONCAT so that you can see the spaces.
SQL
SELECT CONCAT('[', LTRIM(' IP 065 '), ']') AS L, CONCAT('[', RTRIM(' IP 065 '), ']') AS R, CONCAT('[', TRIM(' IP 065 '), ']') AS T;Output
+-------------+-------------+----------+ | L | R | T | +-------------+-------------+----------+ | [IP 065 ] | [ IP 065] | [IP 065] | +-------------+-------------+----------+
Text functions on a table
SQL
SELECT * FROM CUSTOMER;
Output
+-----+--------------+--------------------+-----------+------------+ | CID | CName | Email | City | Phone | +-----+--------------+--------------------+-----------+------------+ | 1 | aditi sharma | aditi.s@gmail.com | Jaipur | 9876501234 | | 2 | Rohit Verma | rohit_v@yahoo.in | Delhi | 9812345678 | | 3 | NEHA GUPTA | neha.g@outlook.com | Pune | 9900011122 | | 4 | Karan Mehta | karan.m@gmail.com | Bhopal | 9765432109 | +-----+--------------+--------------------+-----------+------------+
SQL
SELECT UCASE(CName) AS Name, LENGTH(CName) AS Letters FROM CUSTOMER;
Output
+--------------+---------+ | Name | Letters | +--------------+---------+ | ADITI SHARMA | 12 | | ROHIT VERMA | 11 | | NEHA GUPTA | 10 | | KARAN MEHTA | 11 | +--------------+---------+
SQL
SELECT CName, LEFT(Phone, 5) AS First5, RIGHT(Phone, 4) AS Last4 FROM CUSTOMER;
Output
+--------------+--------+-------+ | CName | First5 | Last4 | +--------------+--------+-------+ | aditi sharma | 98765 | 1234 | | Rohit Verma | 98123 | 5678 | | NEHA GUPTA | 99000 | 1122 | | Karan Mehta | 97654 | 2109 | +--------------+--------+-------+
SQL
SELECT CName, MID(Email, INSTR(Email, '@') + 1) AS Domain FROM CUSTOMER;
Output
+--------------+-------------+ | CName | Domain | +--------------+-------------+ | aditi sharma | gmail.com | | Rohit Verma | yahoo.in | | NEHA GUPTA | outlook.com | | Karan Mehta | gmail.com | +--------------+-------------+
SQL
SELECT CONCAT('[', City, ']') AS Raw, CONCAT('[', TRIM(City), ']') AS Clean FROM CUSTOMER;Output
+-------------+----------+ | Raw | Clean | +-------------+----------+ | [ Jaipur ] | [Jaipur] | | [Delhi] | [Delhi] | | [Pune ] | [Pune] | | [ Bhopal] | [Bhopal] | +-------------+----------+
SQL
SELECT CName FROM CUSTOMER WHERE RIGHT(Email, 9) = 'gmail.com';
Output
+--------------+ | CName | +--------------+ | aditi sharma | | Karan Mehta | +--------------+
SQL
SELECT CONCAT(UCASE(LEFT(CName, 1)), LCASE(MID(CName, 2))) AS Proper FROM CUSTOMER;
Output
+--------------+ | Proper | +--------------+ | Aditi sharma | | Rohit verma | | Neha gupta | | Karan mehta | +--------------+
Summary table
| Function | Returns | Example → Result |
|---|---|---|
| UCASE(s) / UPPER(s) | Capital letters | UCASE('ip') → IP |
| LCASE(s) / LOWER(s) | Small letters | LCASE('IP') → ip |
| LENGTH(s) | Number of characters | LENGTH('Hello') → 5 |
| LEFT(s, n) | First n characters | LEFT('Hello', 2) → He |
| RIGHT(s, n) | Last n characters | RIGHT('Hello', 3) → llo |
| MID / SUBSTR(s, p, n) | n characters from position p | MID('Hello', 2, 3) → ell |
| INSTR(s, sub) | First position of sub (0 if absent) | INSTR('Hello', 'l') → 3 |
| LTRIM / RTRIM / TRIM(s) | Remove leading / trailing / both spaces | TRIM(' Hi ') → Hi |
Frequently Asked Questions
Does SQL count string positions from 0 or 1?
From 1. In MID("Informatics", 3, 4) the third character is "f", so the result is "form". Python counts from 0, so be careful.
What does INSTR return if the substring is not found?
0, because positions start from 1.
What is the difference between TRIM, LTRIM and RTRIM?
LTRIM removes spaces from the left (leading), RTRIM from the right (trailing), and TRIM from both sides. Spaces in the middle stay.
