SQL Functions — String, Numeric, Date & Conversion

-- LENGTH / CHAR_LENGTH: count characters
SELECT LENGTH('Hello World');          -- 11 (PostgreSQL/MySQL)
SELECT LEN('Hello World');             -- 11 (SQL Server)
SELECT LENGTH(Name), Name FROM Employee;

-- UPPER / LOWER: change case
SELECT UPPER('hello world');           -- 'HELLO WORLD'
SELECT LOWER('RAVI KUMAR');            -- 'ravi kumar'
SELECT INITCAP('ravi kumar');          -- 'Ravi Kumar' (PostgreSQL)

-- TRIM / LTRIM / RTRIM: remove whitespace
SELECT TRIM('  hello  ');              -- 'hello' (both sides)
SELECT LTRIM('  hello  ');             -- 'hello  ' (left only)
SELECT RTRIM('  hello  ');             -- '  hello' (right only)
SELECT TRIM(BOTH 'x' FROM 'xxxhelloxx'); -- 'hello' (specific char)

-- SUBSTRING / SUBSTR: extract part of string
SELECT SUBSTRING('Hello World', 1, 5);  -- 'Hello' (pos 1, length 5)
SELECT SUBSTR('Hello World', 7);         -- 'World'  (from pos 7 to end)
SELECT LEFT('Hello World', 5);           -- 'Hello'  (SQL Server/MySQL)
SELECT RIGHT('Hello World', 5);          -- 'World'

-- CONCAT: join strings
SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Employee;
SELECT FirstName || ' ' || LastName    AS FullName FROM Employee;  -- PostgreSQL

-- CONCAT_WS: concat with separator (skips NULLs)
SELECT CONCAT_WS(', ', City, State, Country) AS Address FROM Customer;

-- REPLACE: find and replace
SELECT REPLACE('Hello World', 'World', 'SQL');  -- 'Hello SQL'
UPDATE Employee SET Email = REPLACE(Email, '@old.com', '@new.com');

-- POSITION / INSTR: find substring position
SELECT POSITION('@' IN 'ravi@gmail.com');   -- 5 (PostgreSQL)
SELECT INSTR('ravi@gmail.com', '@');         -- 5 (MySQL/Oracle)
SELECT CHARINDEX('@', 'ravi@gmail.com');     -- 5 (SQL Server)

-- LPAD / RPAD: pad string to fixed length
SELECT LPAD(EmpID::TEXT, 6, '0') AS EmpCode FROM Employee;  -- '000101'
SELECT RPAD(Name, 20, '.') AS PaddedName FROM Employee;     -- 'Ravi............'

-- REVERSE: reverse a string
SELECT REVERSE('hello');  -- 'olleh'

-- REPEAT: repeat a string
SELECT REPEAT('ab', 3);   -- 'ababab'

-- SPLIT_PART (PostgreSQL): split by delimiter, get nth part
SELECT SPLIT_PART('ravi@gmail.com', '@', 1);  -- 'ravi'
SELECT SPLIT_PART('ravi@gmail.com', '@', 2);  -- 'gmail.com'

-- FORMAT numbers as strings
SELECT FORMAT(1234567.89, 2);   -- '1,234,567.89' (MySQL)
SELECT TO_CHAR(1234567.89, 'FM999,999,999.00');  -- PostgreSQL

-- Practical query: normalise and clean name data
UPDATE Customer SET
    Name  = TRIM(UPPER(SUBSTRING(Name, 1, 1)) || LOWER(SUBSTRING(Name, 2))),
    Email = TRIM(LOWER(Email))
WHERE Email IS NOT NULL;

-- ROUND: round to n decimal places
SELECT ROUND(3.14159, 2);      -- 3.14
SELECT ROUND(3.145, 2);        -- 3.15 (rounds half up in most DBMS)
SELECT ROUND(Salary, -3)       -- Round salary to nearest 1000
FROM Employee;                 -- 47823 → 48000

-- FLOOR / CEIL: round down / up to integer
SELECT FLOOR(4.9);    -- 4    (always rounds DOWN)
SELECT CEIL(4.1);     -- 5    (always rounds UP) -- CEILING in SQL Server
SELECT FLOOR(-4.1);   -- -5   (note: floor of negative rounds more negative)
SELECT CEIL(-4.9);    -- -4

-- TRUNC: truncate (drop decimals, no rounding)
SELECT TRUNC(3.99);      -- 3   (PostgreSQL/Oracle)
SELECT TRUNC(3.99, 1);   -- 3.9

-- ABS: absolute value
SELECT ABS(-42);          -- 42
SELECT ABS(Salary - AvgSalary) AS Deviation FROM Employee;

-- POWER / SQRT: exponentiation
SELECT POWER(2, 10);    -- 1024
SELECT SQRT(144);        -- 12

-- MOD: modulo (remainder)
SELECT MOD(17, 5);       -- 2  (17 = 5*3 + 2)
SELECT 17 % 5;           -- 2  (SQL Server / PostgreSQL)

-- RANDOM / RAND: random number [0, 1)
SELECT RANDOM();          -- PostgreSQL: random float 0-1
SELECT RAND();            -- MySQL
SELECT RAND(42);          -- Seeded for reproducibility (MySQL)

-- Practical: bucket rows into 10 groups randomly
SELECT EmpID, Name, FLOOR(RANDOM() * 10) + 1 AS Bucket
FROM Employee;

-- SIGN: returns -1, 0, or 1
SELECT SIGN(-42);  -- -1    SELECT SIGN(0);  -- 0    SELECT SIGN(5);  -- 1

-- DIV: integer division (MySQL)
SELECT 17 DIV 5;   -- 3 (quotient only, no remainder)

-- Current date and time
SELECT NOW();                  -- '2024-12-25 14:30:00' (date + time, PostgreSQL/MySQL)
SELECT CURRENT_TIMESTAMP;      -- ANSI standard equivalent
SELECT CURRENT_DATE;           -- '2024-12-25' (date only)
SELECT CURRENT_TIME;           -- '14:30:00' (time only)
SELECT SYSDATE FROM DUAL;      -- Oracle

-- Extracting parts
SELECT EXTRACT(YEAR  FROM NOW());     -- 2024
SELECT EXTRACT(MONTH FROM NOW());     -- 12
SELECT EXTRACT(DAY   FROM NOW());     -- 25
SELECT EXTRACT(HOUR  FROM NOW());     -- 14
SELECT EXTRACT(DOW   FROM NOW());     -- 3 (day of week: 0=Sunday)
SELECT EXTRACT(WEEK  FROM NOW());     -- 52

-- DATE_PART (PostgreSQL equivalent to EXTRACT)
SELECT DATE_PART('year', OrderDate) AS Year FROM Orders;

-- Date arithmetic (add/subtract intervals)
SELECT NOW() + INTERVAL '7 days';             -- One week from now
SELECT NOW() - INTERVAL '1 month';            -- One month ago
SELECT OrderDate + INTERVAL '30 days' AS DueDate FROM Orders;

-- DATEADD / DATE_ADD
SELECT DATEADD(day, 30, OrderDate) FROM Orders;  -- SQL Server
SELECT DATE_ADD(OrderDate, INTERVAL 30 DAY)    FROM Orders;  -- MySQL

-- DATEDIFF: days between two dates
SELECT DATEDIFF('2024-12-31', '2024-01-01');   -- 365 (MySQL)
SELECT '2024-12-31'::DATE - '2024-01-01'::DATE; -- 365 (PostgreSQL)
SELECT DATEDIFF(day, JoinDate, GETDATE()) AS DaysEmployed FROM Employee; -- SQL Server

-- Date formatting
SELECT TO_CHAR(NOW(), 'DD Month YYYY')   AS Formatted;  -- '25 December 2024' (PostgreSQL)
SELECT DATE_FORMAT(NOW(), '%d %M %Y')    AS Formatted;  -- MySQL
SELECT FORMAT(NOW(), 'dd MMMM yyyy')     AS Formatted;  -- SQL Server

-- Date truncation (useful for grouping by month/week)
SELECT DATE_TRUNC('month', OrderDate) AS Month, SUM(Amount)
FROM Orders GROUP BY DATE_TRUNC('month', OrderDate);

-- Age calculation
SELECT AGE(NOW(), DOB) AS Age FROM Customer;    -- PostgreSQL: '25 years 3 mons 10 days'
SELECT TIMESTAMPDIFF(YEAR, DOB, NOW()) AS Age FROM Customer;  -- MySQL (years only)

-- Convert string to date
SELECT TO_DATE('2024-12-25', 'YYYY-MM-DD');    -- PostgreSQL
SELECT STR_TO_DATE('25/12/2024', '%d/%m/%Y');  -- MySQL
SELECT CAST('2024-12-25' AS DATE);              -- ANSI standard

-- CAST: explicit type conversion (ANSI standard — works everywhere)
SELECT CAST('2024-12-25' AS DATE);
SELECT CAST(42.99 AS INT);              -- 42 (truncates decimal)
SELECT CAST(Salary AS VARCHAR(20))      FROM Employee;

-- :: operator (PostgreSQL shorthand for CAST)
SELECT '42'::INT, 3.14::TEXT, '2024-01-01'::DATE;

-- CONVERT (SQL Server / MySQL)
SELECT CONVERT(DATE, '2024-12-25');              -- SQL Server
SELECT CONVERT('2024-12-25', DATE);              -- MySQL

-- COALESCE: return first non-NULL value (ANSI standard)
SELECT COALESCE(MiddleName, 'N/A') AS MiddleName FROM Employee;
SELECT COALESCE(Phone, Mobile, 'No contact') AS Contact FROM Customer;
-- Use case: replace NULL with a default value

-- NULLIF: return NULL if two values are equal
SELECT NULLIF(Score, 0)  -- Returns NULL if Score = 0 (avoids division by zero)
SELECT 100.0 / NULLIF(TotalItems, 0) AS AvgPrice FROM Orders;

-- NVL (Oracle equivalent to COALESCE for 2 args)
SELECT NVL(Commission, 0) FROM SalesRep;

-- ISNULL (SQL Server), IFNULL (MySQL)
SELECT ISNULL(Commission, 0) FROM SalesRep;   -- SQL Server
SELECT IFNULL(Commission, 0) FROM SalesRep;   -- MySQL

-- IIF / CASE: conditional expression
SELECT IIF(Salary > 50000, 'Senior', 'Junior') AS Grade FROM Employee;  -- SQL Server
SELECT CASE WHEN Salary > 50000 THEN 'Senior' ELSE 'Junior' END AS Grade FROM Employee;