SQL String Functions 🔤

Mentor's Note: Data in the real world is messy. People type their names in lowercase, add extra spaces, or provide long codes where you only need the first 3 letters. String functions are your "Formatting Tools" to clean this up. 💡

---

🌟 The Scenario: The Name Badge Maker 📛

Imagine you are printing name badges for a conference.

• The Problem: A user typed their name as " vISHNU damwala ".
• The Action:
  1. TRIM: Remove the extra spaces. ✂️
  2. UPPER: Make it all capitals for visibility. ⬆️
• The Result: "VISHNU DAMWALA" ✅

---

💻 1. Case Conversion (UPPER & LOWER)

Used to standardize text casing.

-- Scenario: Find a user regardless of how they typed their name
SELECT * FROM users
WHERE UPPER(name) = 'VISHNU';

-- Output: 'vishnu', 'Vishnu', and 'VISHNU' will all match!

---

💻 2. Cleanup (TRIM, LTRIM, RTRIM) ✂️

Removes leading and trailing spaces.

-- Scenario: Clean up messy data imports
SELECT TRIM('   Hello   ') AS "Clean" FROM dual;
-- Result: 'Hello'

---

💻 3. Extraction (SUBSTR / SUBSTRING) 🍰

Used to "slice" a piece of text.

Oracle

-- SUBSTR(string, start, length)
SELECT SUBSTR('VD-SURAT', 1, 2) FROM dual;
-- Result: 'VD'

MySQL

-- SUBSTRING(string, start, length)
SELECT SUBSTRING('VD-SURAT', 1, 2);
-- Result: 'VD'

---

💻 4. Length & Search (LENGTH & INSTR) 📏

• LENGTH: Counts characters.
• INSTR: Finds the position of a character.

-- Scenario: Find the position of the '@' in an email
SELECT INSTR('[email protected]', '@') FROM dual;
-- Result: 7

---

🎨 Visual Logic: The Slicer

Input: [ S | U | R | A | T ]
Index:   1   2   3   4   5
Action: SUBSTR(str, 1, 2)
Result: [ S | U ]

---

📈 Learning Path

• ← Back: Reference Hub
• Next: Quick Reference Sheet →