--Scalar Functions (Sting Functions)
-- Negative to Positive
SELECT ABS(-0.555);
-- Output: 0.555
-- Round value (மட்டம் தட்டும்)
SELECT ROUND(-0.555);
-- Output: -1
-- Modulus (பிரித்தல் வந்த மீதி)
SELECT MOD(20,2);
-- Output: 0 (20 ÷ 2 = 10, remainder 0)
-- Power (அடுக்குச் சக்தி)
SELECT POW(2,50);
SELECT POWER(2,50);
-- Output: very large number (1125899906842624)
-- Random number between 0 and 1
SELECT RAND();
-- Division (quotient)
SELECT 10 DIV 5;
-- Output: 2
-- Truncate (remove extra decimals, only 2 kept)
SELECT TRUNCATE(100.123435,2);
-- Output: 100.12
-- Concatenate strings
SELECT CONCAT('Hello', ' ', 'World') AS Combined;
-- Reverse a string
SELECT REVERSE('MySQL') AS Reversed; -- Output: LQSyM
-- Replace part of a string
SELECT REPLACE('I like Java', 'Java', 'SQL') AS Replaced;
-- Trim spaces
SELECT TRIM(' Hello ') AS Trimmed;
-- Substring (part of string)
SELECT SUBSTRING('Database', 1, 4) AS SubPart; -- Output: Data
-- Locate position of substring
SELECT LOCATE('SQL', 'Learn MySQL Fast') AS Position;
-- Ceiling (next highest integer)
SELECT CEIL(12.3) AS CeilValue; -- Output: 13
-- Floor (next lowest integer)
SELECT FLOOR(12.9) AS FloorValue; -- Output: 12
-- Sign of number (-1, 0, 1)
SELECT SIGN(-15) AS SignValue;
-- Rand (random number between 0 and 1)
SELECT RAND() AS RandomNumber;
-- Negative to Positive (Absolute value)
SELECT ABS(-0.555);
-- Output: 0.555
-- Round value (மட்டம் தட்டும்)
SELECT ROUND(-0.555);
-- Output: -1
-- Modulus (பிரித்தல் வந்த மீதி)
SELECT MOD(20,2);
-- Output: 0 (20 ÷ 2 = 10, remainder 0)
-- Power (அடுக்குச் சக்தி)
SELECT POW(2,10);
SELECT POWER(2,10);
-- Output: 1024
-- Square root (வேர் மதிப்பு)
SELECT SQRT(49);
-- Output: 7
-- Random number between 0 and 1
SELECT RAND();
-- Division (quotient only)
SELECT 10 DIV 3;
-- Output: 3 (integer division)
-- Truncate (remove extra decimals)
SELECT TRUNCATE(100.123435,2);
-- Output: 100.12
-- Ceiling (மேல் முழு எண்)
SELECT CEIL(12.3);
-- Output: 13
-- Floor (கீழ் முழு எண்)
SELECT FLOOR(12.9);
-- Output: 12
-- Sign (negative / zero / positive check)
SELECT SIGN(-15), SIGN(0), SIGN(25);
-- Output: -1, 0, 1
-- Length of string
SELECT LENGTH('Hello World');
-- Output: 11
-- First 5 characters
SELECT LEFT('MySQLDatabase', 5);
-- Output: MySQL
-- Last 8 characters
SELECT RIGHT('MySQLDatabase', 8);
-- Output: Database
-- Convert to lowercase
SELECT LOWER('HELLO MYSQL');
-- Output: hello mysql
-- Convert to uppercase
SELECT UPPER('hello mysql');
-- Output: HELLO MYSQL
-- Remove spaces
SELECT TRIM(' Hello ');
-- Output: Hello
-- Substring (part of text)
SELECT SUBSTRING('Database', 1, 4);
-- Output: Data
-- Replace part of text
SELECT REPLACE('I like Java', 'Java', 'SQL');
-- Output: I like SQL
-- Reverse text
SELECT REVERSE('MySQL');
-- Output: LQSyM
-- Repeat a string
SELECT REPEAT('SQL', 3);
-- Output: SQLSQLSQL
-- Insert text into another string (position, length to replace, new text)
SELECT INSERT('HelloWorld', 6, 0, ' MySQL ');
-- Output: Hello MySQL World
-- Locate position of substring (case sensitive)
SELECT LOCATE('SQL', 'Learn SQL with MySQL');
-- Output: 7
-- Locate position (case insensitive)
SELECT INSTR('Learn sql with MYSQL', 'sql');
-- Output: 7
-- Lpad (pad left side with given character until length is reached)
SELECT LPAD('123', 5, '0');
-- Output: 00123
-- Rpad (pad right side with given character until length is reached)
SELECT RPAD('123', 5, '0');
-- Output: 12300
-- ASCII value of first character
SELECT ASCII('A');
-- Output: 65
-- CHAR() – opposite of ASCII
SELECT CHAR(65);
-- Output: A
-- Space (create blank spaces)
SELECT CONCAT('Hello', SPACE(5), 'World');
-- Output: Hello World
-- Field – find position of value in list
SELECT FIELD('B', 'A','B','C','D');
-- Output: 2 (B is 2nd item in the list)
-- ELT – return element by position
SELECT ELT(3, 'Apple','Banana','Mango','Orange');
-- Output: Mango
-- CHAR_LENGTH() → number of characters (not bytes)
SELECT CHAR_LENGTH('MySQL');
-- Output: 5
-- LENGTH() → number of bytes
SELECT LENGTH('MySQL');
-- Output: 5 (same here, but different in multibyte chars like Unicode)
-- MAKE_SET() → return string from list based on bits set
SELECT MAKE_SET(5,'a','b','c','d');
-- Output: a,c (5 in binary is 0101 → picks 1st and 3rd)
-- FIND_IN_SET() → find position of a value in a comma-separated list
SELECT FIND_IN_SET('b','a,b,c,d');
-- Output: 2
-- EXPORT_SET() → show bits as ON/OFF string
SELECT EXPORT_SET(5,'Y','N',',',4);
-- Output: Y,N,Y,N
-- FORMAT() → number with commas (as string)
SELECT FORMAT(1234567.89,2);
-- Output: 1,234,567.89
-- HEX() → convert to hex value
SELECT HEX('MySQL');
-- Output: 4D7953514C
-- UNHEX() → reverse of HEX
SELECT UNHEX('4D7953514C');
-- Output: MySQL
-- QUOTE() → escape special characters with quotes
SELECT QUOTE("It's a test");
-- Output: 'It\'s a test'
-- ORD() → ASCII of first character (same as ASCII, but extended for multi-byte)
SELECT ORD('ABC');
-- Output: 65
-- REVERSE() → reverse string
SELECT REVERSE('Database');
-- Output: esabataD
-- SUBSTRING_INDEX(string, delimiter, count)
SELECT SUBSTRING_INDEX('a,b,c,d', ',', 2);
-- Output: a,b
SELECT SUBSTRING_INDEX('a,b,c,d', ',', -2);
-- Output: c,d
-- OCTET_LENGTH() → alias of LENGTH()
SELECT OCTET_LENGTH('Hello');
-- Output: 5
-- BIT_LENGTH() → total bits in string
SELECT BIT_LENGTH('AB');
-- Output: 16 (2 chars × 8 bits)