🛢️ Unit 2 – SQL से Database Query · Lesson 02
SQL Text Functions
Character positions
Text functions strings पर काम करते हैं। SQL में पहला character position 1 पर होता है; negative position दाएँ छोर से गिनती है।
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 भी characters हैं, इसलिए 'Hello World' में 11 characters हैं।
SQL
SELECT LENGTH('Informatics'), LENGTH('Hello World'), LENGTH('');Output
+-----------------------+-----------------------+------------+
| LENGTH('Informatics') | LENGTH('Hello World') | LENGTH('') |
+-----------------------+-----------------------+------------+
| 11 | 11 | 0 |
+-----------------------+-----------------------+------------+LEFT और 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) pos से शुरू करके n characters निकालता है। SUBSTR और SUBSTRING यही function हैं। n न दें तो अंत तक सब लेता है।
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
Spaces दिखाने के लिए CONCAT से brackets जोड़े गए हैं।
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] | +-------------+-------------+----------+
Table पर text functions
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 |
अक्सर पूछे जाने वाले प्रश्न (FAQ)
SQL string positions 0 से गिनता है या 1 से?
1 से। MID("Informatics", 3, 4) में तीसरा character "f" है, इसलिए result "form" है। Python 0 से गिनता है, ध्यान रखें।
Substring न मिले तो INSTR क्या लौटाता है?
0, क्योंकि positions 1 से शुरू होती हैं।
TRIM, LTRIM और RTRIM में क्या फ़र्क है?
LTRIM बाईं (leading) spaces हटाता है, RTRIM दाईं (trailing), और TRIM दोनों तरफ़ की। बीच की spaces रहती हैं।
