Essential SQL Functions: String, Date, and Numeric

A reference guide to key SQL built-in functions for strings (UPPER, CONCAT), dates (NOW, DATEDIFF), and numbers (ROUND, FLOOR, MOD).

3 min read

SQL provides various built-in functions for processing and calculating data. This article introduces the key functions for strings, dates/times, and numbers.

1. String Functions

FunctionDescriptionExample
UPPER(string)Converts a string to all uppercase.SELECT UPPER('hello'); -> HELLO
LOWER(string)Converts a string to all lowercase.SELECT LOWER('WORLD'); -> world
SUBSTRING(string, start, length)Extracts a substring from a specified position with a specified length.SELECT SUBSTRING('abcdef', 2, 3); -> bcd
INSTR(target, search)Returns the position of the first occurrence of the search string in the target. Returns 0 if not found.SELECT INSTR('hello world', 'world'); -> 7
LENGTH(string)Returns the length of a string.SELECT LENGTH('example'); -> 7
CONCAT(str1, str2, ...)Concatenates multiple strings.SELECT CONCAT('Hello', ' ', 'World'); -> Hello World
RPAD(string, total_length, pad_char)Pads the right side of a string with the specified character until it reaches the total length.SELECT RPAD('abc', 5, '*'); -> abc**
LPAD(string, total_length, pad_char)Pads the left side of a string with the specified character until it reaches the total length.SELECT LPAD('abc', 5, '*'); -> **abc
TRIM([LEADING|TRAILING|BOTH] [trim_char FROM] string)Removes specified characters from the beginning, end, or both sides of a string. Defaults to whitespace.SELECT TRIM(' hello '); -> hello
REPLACE(string, search, replacement)Replaces all occurrences of the search string with the replacement string.SELECT REPLACE('hello world', 'o', 'a'); -> hella warld

2. Date/Time Functions

FunctionDescriptionExample
NOW()Returns the current date and time.SELECT NOW(); -> 2023-10-27 10:30:00
CURDATE()Returns the current date.SELECT CURDATE(); -> 2023-10-27
CURTIME()Returns the current time.SELECT CURTIME(); -> 10:30:00
DATEDIFF(date1, date2)Returns the number of days between date1 and date2.SELECT DATEDIFF('2023-10-31', '2023-10-27'); -> 4
DATE_ADD(date, INTERVAL value unit)Returns a date with a specified interval added. Units include DAY, MONTH, YEAR, etc.SELECT DATE_ADD('2023-10-27', INTERVAL 5 DAY); -> 2023-11-01
DATE_SUB(date, INTERVAL value unit)Returns a date with a specified interval subtracted.SELECT DATE_SUB('2023-10-27', INTERVAL 1 MONTH); -> 2023-09-27
LAST_DAY(date)Returns the last day of the month for the given date.SELECT LAST_DAY('2023-02-15'); -> 2023-02-28
YEAR(date)Extracts the year from a date.SELECT YEAR('2023-10-27'); -> 2023
MONTH(date)Extracts the month from a date.SELECT MONTH('2023-10-27'); -> 10
DAY(date)Extracts the day from a date.SELECT DAY('2023-10-27'); -> 27

3. Numeric Functions

FunctionDescriptionExample
ROUND(number, n)Rounds a number to n decimal places. Rounds to integer if n is omitted.SELECT ROUND(123.456, 2); -> 123.46
TRUNCATE(number, n)Truncates a number to n decimal places.SELECT TRUNCATE(123.456, 2); -> 123.45
CEIL(number) / CEILING(number)Rounds up to the smallest integer greater than or equal to the number.SELECT CEIL(123.45); -> 124
FLOOR(number)Rounds down to the largest integer less than or equal to the number.SELECT FLOOR(123.45); -> 123
MOD(number1, number2)Returns the remainder of number1 divided by number2.SELECT MOD(10, 3); -> 1
ABS(number)Returns the absolute value of a number.SELECT ABS(-10); -> 10
POW(number, exponent) / POWER(number, exponent)Raises a number to the specified power.SELECT POW(2, 3); -> 8
SQRT(number)Returns the square root of a number.SELECT SQRT(9); -> 3

These functions may have slightly different names or behaviors depending on the RDBMS (MySQL, PostgreSQL, Oracle, SQL Server, etc.). It is recommended to check the official documentation of your RDBMS.

関連記事