SQL String Functions 🔤¶

Prerequisites: SELECT Statement

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.

OracleMySQL

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

-- 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 ]