Built-in Functions

🧰 SQL Built-in Functions: Your Magic Toolbox

Imagine you have a giant toolbox. Inside are special tools that can change words, do math, and play with dates. SQL has these tools too! They’re called Built-in Functions.

🎯 The Big Picture

Think of a database like a huge toy box with lots of toys (data). Sometimes you want to:

• Change how a toy looks (string functions)
• Count your toys (numeric functions)
• Know when you got a toy (date functions)
• Transform one toy into another (type conversion)

SQL gives you magic spells (functions) to do all this!

📝 String Manipulation Functions

What Are They?

String functions are like word scissors and glue. They let you cut, paste, change, and play with text!

🔧 The Main Tools

UPPER() and LOWER() - The Case Changers

SELECT UPPER('hello');
-- Result: HELLO

SELECT LOWER('WORLD');
-- Result: world

Like a megaphone (UPPER) or a whisper (LOWER)!

CONCAT() - The Glue Gun

SELECT CONCAT('Hello', ' ', 'World');
-- Result: Hello World

Sticks words together like friendship bracelets!

SUBSTRING() - The Cookie Cutter

SELECT SUBSTRING('Dinosaur', 1, 4);
-- Result: Dino

Cuts out a piece from position 1, taking 4 letters.

LENGTH() - The Measuring Tape

SELECT LENGTH('Rainbow');
-- Result: 7

Counts every single character!

TRIM() - The Scissors

SELECT TRIM('  hello  ');
-- Result: hello

Cuts off extra spaces from both ends!

REPLACE() - The Find & Swap

SELECT REPLACE('I love cats', 'cats', 'dogs');
-- Result: I love dogs

Finds a word and swaps it for another!

🔢 Numeric Functions

What Are They?

These are your math helpers! They round, calculate, and crunch numbers.

🔧 The Main Tools

ROUND() - The Rounder

SELECT ROUND(3.7);
-- Result: 4

SELECT ROUND(3.14159, 2);
-- Result: 3.14

Rounds to the nearest whole number (or decimals you choose)!

CEIL() and FLOOR() - Up and Down

SELECT CEIL(4.1);
-- Result: 5 (always goes UP)

SELECT FLOOR(4.9);
-- Result: 4 (always goes DOWN)

CEIL = elevator going up. FLOOR = elevator going down.

ABS() - The Positive Maker

SELECT ABS(-15);
-- Result: 15

Removes the minus sign! Always positive!

MOD() - The Remainder Finder

SELECT MOD(10, 3);
-- Result: 1

10 divided by 3 = 3 with 1 left over. MOD gives you that leftover!

POWER() - The Super Multiplier

SELECT POWER(2, 3);
-- Result: 8

2 × 2 × 2 = 8. It multiplies 2 by itself 3 times!

📅 Date Part Extraction

What Is It?

Imagine a birthday cake. The date is like knowing:

• Which year the party happened
• Which month it was
• Which day everyone sang

SQL lets you grab just ONE piece from a date!

🔧 The Main Tools

YEAR(), MONTH(), DAY()

SELECT YEAR('2024-12-25');
-- Result: 2024

SELECT MONTH('2024-12-25');
-- Result: 12

SELECT DAY('2024-12-25');
-- Result: 25

EXTRACT() - The Universal Picker

SELECT EXTRACT(MONTH FROM '2024-12-25');
-- Result: 12

SELECT EXTRACT(HOUR FROM '14:30:00');
-- Result: 14

Works for years, months, days, hours, minutes, seconds!

➕ Date Arithmetic

What Is It?

Sometimes you need to figure out:

• “What date is 7 days from now?”
• “How many days between two birthdays?”

Date arithmetic is math with dates!

🔧 The Main Tools

Adding Days

SELECT DATE_ADD('2024-01-01', INTERVAL 30 DAY);
-- Result: 2024-01-31

Add 30 days to January 1st!

Subtracting Days

SELECT DATE_SUB('2024-12-25', INTERVAL 7 DAY);
-- Result: 2024-12-18

Go back 7 days from Christmas!

Finding the Difference

SELECT DATEDIFF('2024-12-31', '2024-01-01');
-- Result: 365

How many days between two dates?

⏰ Current DateTime Functions

What Are They?

These functions tell you what time it is RIGHT NOW in the database!

🔧 The Main Tools

NOW() - The Full Clock

SELECT NOW();
-- Result: 2024-12-02 14:30:45

Shows date AND time, right this second!

CURDATE() - Just Today

SELECT CURDATE();
-- Result: 2024-12-02

Only the date, no time!

CURTIME() - Just the Clock

SELECT CURTIME();
-- Result: 14:30:45

Only the time, no date!

🎨 Date Formatting

What Is It?

Dates can look different around the world:

• December 2, 2024
• 2024-12-02
• 02/12/2024

Formatting lets you choose how dates look!

🔧 The Main Tool

DATE_FORMAT() - The Costume Designer

SELECT DATE_FORMAT('2024-12-25', '%M %d, %Y');
-- Result: December 25, 2024

SELECT DATE_FORMAT('2024-12-25', '%d/%m/%Y');
-- Result: 25/12/2024

📋 Format Codes Cheat Sheet

🔄 CAST and CONVERT

What Are They?

Sometimes data is wearing the wrong costume. A number might be dressed as text! CAST and CONVERT let you change the costume.

🔧 The Main Tools

CAST() - The Transformer

SELECT CAST('123' AS INTEGER);
-- Result: 123 (now it's a real number!)

SELECT CAST(456 AS VARCHAR);
-- Result: '456' (now it's text!)

SELECT CAST('2024-12-25' AS DATE);
-- Result: 2024-12-25 (now it's a date!)

CONVERT() - The Alternative Transformer

SELECT CONVERT('100', INTEGER);
-- Result: 100

SELECT CONVERT(NOW(), DATE);
-- Result: 2024-12-02 (just the date part)

📏 Type Conversion Rules

The Golden Rules

1. Numbers to Text ✅ Always works!

   SELECT CAST(42 AS VARCHAR);
   -- Result: '42'
   

2. Text to Numbers ⚠️ Only if text looks like a number!

   SELECT CAST('99' AS INTEGER);
   -- Result: 99 ✅
   
   SELECT CAST('hello' AS INTEGER);
   -- ERROR! ❌ 'hello' isn't a number!
   

3. Dates to Text ✅ Always works!

   SELECT CAST(CURDATE() AS VARCHAR);
   -- Result: '2024-12-02'
   

4. Text to Dates ⚠️ Must be formatted correctly!

   SELECT CAST('2024-12-25' AS DATE);
   -- Result: 2024-12-25 ✅
   
   SELECT CAST('Christmas' AS DATE);
   -- ERROR! ❌
   

🎭 The Conversion Flow

graph TD
    A["Any Data Type"] --> B{CAST or CONVERT}
    B --> C["INTEGER"]
    B --> D["VARCHAR"]
    B --> E["DATE"]
    B --> F["DECIMAL"]
    C --> G["Math Operations"]
    D --> H["Text Operations"]
    E --> I["Date Operations"]

🏆 Summary: Your Function Toolkit

💡 Remember!

Functions are like magic spells in SQL. Each one does something special. The more you practice, the more powerful you become!

Start with simple ones like UPPER() and ROUND(). Soon you’ll be combining them like a true SQL wizard! 🧙‍♂️