Ana Iglesias (Universidad Politécnica de Madrid)
2021-09-07
Thesaurus that gathers information of functions from MySQL
Functions taxonomy
Returns the absolute value of a number
ABS
Returns the arc cosine of a number
ACOS
Adds a time/date interval to a date and then returns the date
ADDDATE
Adds a time interval to a time/datetime and then returns the time/datetime
ADDTIME
Returns the ASCII value for the specific character
ASCII
Returns the arc sine of a number
ASIN
Returns the arc tangent of one or two numbers
ATAN
Returns the arc tangent of two numbers
ATAN2
Returns the average value of an expression
AVG
Returns the number of bits in a string.
BIT_LENGTH
The bitwise AND operation
BITAND
Returns true if and only if the first parameter has a bit set in the position specified by the second parameter.
BITGET
The bitwise NOT operation
BITNOT
The bitwise OR operation
BITOR
The bitwise XOR operation
BITXOR
Block distance
Returns the smallest integer value that is >= to a number
CEIL
Returns the smallest integer value that is >= to a number
CEILING
Returns the character that represents the ASCII value
CHAR
Returns the length of a string (in characters)
CHAR_LENGTH
Returns the length of a string (in characters)
CHARACTER_LENGTH
Returns s1 without s2 (it s2 is in s1, else returns s1 as it was)
chomp
Compresses the data using the specified compression algorithm
COMPRESS
Adds two or more expressions together
CONCAT
Adds two or more expressions together with a separator
CONCAT_WS
Confusion probability
Checks if s2 is contained in s1 and returns a boolean
contains
Returns the cosine of a number
COS
Calculate the hyperbolic cosine
COSH
Returns the cotangent of a number
COT
Returns the number of records returned by a select query
COUNT
Returns the current date
CURDATE
Returns the current date
CURRENT_DATE
Returns the current time
CURRENT_TIME
Returns the current date and time
CURRENT_TIMESTAMP
Returns the current time
CURTIME
Damerau-Levenshtain distance
Extracts the date part from a datetime expression
DATE
Adds a time/date interval to a date and then returns the date
DATE_ADD
Formats a date
DATE_FORMAT
Returns part of a date.
datePart
Subtracts a time/date interval from a date and then returns the date
DATE_SUB
Returns the number of days between two date values
DATEDIFF
Functions applied over dates
DateTime Functions
Returns the day of the month for a given date
DAY
Returns the weekday name for a given date
DAYNAME
Returns the day of the month for a given date
DAYOFMONTH
Returns the weekday index for a given date
DAYOFWEEK
Returns the day of the year for a given date
DAYOFYEAR
Decrypts data using a key
DECRYPT
Converts a value in radians to degrees
DEGREES
Returns the difference between the sounds of two strings
DIFFERENCE
Used for integer division
DIV
Encrypts data using a key
ENCRYPT
Checks if s1 ends with s2 and returns a boolean
endsWith
Checks if two given values are the same
equal
escape in the given escaping modes
escape
Returns e raised to the power of a specified number
EXP
Expands data that was compressed using the COMPRESS function
EXPAND
Extracts a part from a given date
EXTRACT
Fellegi-Sunters metric
Returns the index position of a value in a list of values
FIELD
Attempts to find the next subsequence of the string that matches the pattern (maintains duplicates)
find
Returns the position of a string within a list of strings
FIND_IN_SET
Returns the largest integer value that is <= to a number
FLOOR
Formats a number to a format like #
FORMAT
Returns a date from a numeric datevalue
FROM_DAYS
Grammar-based distance
Checks if numerical value is greater than another
greater than
Returns the greatest value of the list of arguments
GREATEST
Hamming distance
Calculate the hash value using an algorithm
and repeat this process for a number of iterations
HASH
Hellinger distance
Converts a hex representation of a string to a string
HEXTORAW
Returns the hour part for a given date
HOUR
Returns a date changed by the given amount in the given unit of time. Unit defaults to 'hour'.
inc
Information radius
Inserts a string within a string at the specified position and for a certain number of characters
INSERT
Returns the position of the first occurrence of a string in another string
INSTR
Jaccard simmilarity
Jaro-Winkler distance
Extracts the last day of the month for a given date
LAST_DAY
Returns the index fo s2 last ocurring in s1 as an integer, returns -1 if s2 is not found in s1
lastIndexOf
Converts a string to lower-case
LCASE
Returns the smallest value of the list of arguments
LEAST
Extracts a number of characters from a string (starting from left)
LEFT
Returns the length of a string (in bytes)
LENGTH
Checks if numerical value is less than another
less than
Levenshtain distance
Returns the natural logarithm of a number
LN
Returns the current date and time
LOCALTIME
Returns the current date and time
LOCALTIMESTAMP
Returns the position of the first occurrence of a substring in a string
LOCATE
Returns the natural logarithm of a number
or the logarithm of a number to a specified base
LOG
Returns the natural logarithm of a number to base 10
LOG10
Returns the natural logarithm of a number to base 2
LOG2
Logical operators
Logical Functions
Left-pads a string with another string
to a certain length
LPAD
The bitwise left shift operation
LSHIFT
Removes leading spaces from a string
LTRIM
Creates and returns a date based on a year and a number of days value
MAKEDATE
and second value
minute
Creates and returns a time based on an hour
Attempts to match the string in its entirety against the regex pattern and returns an array of capture groups (removes duplicates) - returning the first match
match
Returns the maximum value in a set of values
MAX
Maximal Matches
return hash in md-5
md5
Returns the microsecond part of a time/datetime
MICROSECOND
Extracts a substring from a string (starting at any position)
MID
Returns the minimum value in a set of values
MIN
Returns the minute part of a time/datetime
MINUTE
Returns the remainder of a number divided by another number
MOD
Returns the month part for a given date
MONTH
Returns the name of the month for a given date
MONTHNAME
Returns the current date and time
NOW
Functions applied over numbers
Numeric Functions
Returns the number of bytes in a string
OCTET_LENGTH
Computes a hash value
ORA_HASH
Overlap coefficient
Parses a string and returns a timestamp
PARSEDATETIME
and b is the substring after frag. If boolean is true
frag is not returned
b ] where a is the substring within string before the first occurrence of frag in string
frag
Adds a specified number of months to a period
PERIOD_ADD
Returns the difference between two periods
PERIOD_DIFF
Returns the value of PI
PI
Returns the position of the first occurrence of a substring in a string
POSITION
Returns the value of a number raised to the power of another number
POW
Returns the value of a number raised to the power of another number
POWER
Returns the quarter of the year for a given date value
QUARTER
Quotes the specified identifier. Identifier is surrounded by double quotes
QUOTE_IDENT
Converts a degree value into radians
RADIANS
Returns a random number
RAND
Returns a new UUID with 122 pseudo random bits
RAND_UUID
Converts a string or bytes to the hex representation
RAWTOHEX
Matches string to a regular expression
REGEXP_LIKE
Replaces each substring that matches a regular expression
REGEXP_REPLACE
Repeats a string as many times as specified
REPEAT
Replaces all occurrences of a substring within a string
with a new substring
REPLACE
Returns the string obtained by replacing any character in s1 that is also in s2 with the character s3
replaceChars
Reverses a string and returns the result
REVERSE
RFIDF distance
Extracts a number of characters from a string (starting from right)
RIGHT
Rounds a number to a specified number of decimal places
ROUND
This function rounds numbers in a good way
but it is slow
ROUNDMAGIC
Right-pads a string with another string
to a certain length
RPAD
and b is the substring after frag. If boolean is true
frag is not returned
b ] where a is the substring within string before the last occurrence of frag in string
frag
The bitwise right shift operation
RSHIFT
Removes trailing spaces from a string
RTRIM
Returns a time value based on the specified seconds
SEC_TO_TIME
Returns the seconds part of a time/datetime
SECOND
Generates a number of cryptographically secure random numbers
SECURE_RAND
return hash in sha-1
sha1
Returns the sign of a number
SIGN
Simple Matching coefficient
Returns the sine of a number
SIN
Calculate the hyperbolic sine
SINH
Skew divergence
Sorensen-Dice coefficient
Returns a four character code representing the sound of a string
SOUNDEX
Returns a string of the specified number of space characters
SPACE
Returns the array of strings obtained by splitting the string at wherever separator is found in it.
split
Returns an array of strings obtained by splitting s into groups of consecutive characters where the characters within each group share the same unicode type
splitByCharType
Returns the array of strings obtained by splitting s into substrings with the given lengths.
splitByLengths
Returns the square root of a number
SQRT
Checks if s1 starts with s2 and returns a boolean
startsWith
Returns a date based on a string and a format
STR_TO_DATE
Compares two strings
STRCMP
Functions applied over strings
String Functions
Functions that measure distance between two strings
String metrics Functions
Converts a encoded string using the Java string literal encoding format
STRINGDECODE
Encodes special characters in a string using the Java string literal encoding format
STRINGENCODE
Encodes a string to a byte array using the UTF8 encoding format
STRINGTOUTF8
Subtracts a time/date interval from a date and then returns the date
SUBDATE
Extracts a substring from a string (starting at any position)
SUBSTRING
Returns a substring of a string before a specified number of delimiter occurs
SUBSTRING_INDEX
Subtracts a time interval from a datetime and then returns the time/datetime
SUBTIME
Calculates the sum of a set of values
SUM
Returns the current date and time
SYSDATE
Returns the tangent of a number
TAN
Calculate the hyperbolic tangent
TANH
Tau metric
Extracts the time part from a given time/datetime
TIME
Formats a time by a specified format
TIME_FORMAT
Converts a time value into seconds
TIME_TO_SEC
Returns the difference between two time/datetime expressions
TIMEDIFF
Returns a datetime value based on a date or datetime value
TIMESTAMP
a number
or text
Oracle-compatible TO_CHAR function that can format a timestamp
Returns the value converted in date. Month_first false if date is formatted with the day before the month. FormatN formats of the input (don't understand this very well)
toDate
Returns the number of days between a date and date 0000-00-00''
TO_DAYS
Converts input to number
toNumber
Transform any data type into a string
toString
Returns the string converted to titlecase (first letter of each word in uppercase)
toTitleCase
Oracle-compatible TRANSLATE function that replaces a sequence of characters in a string with another set of characters
TRANSLATE
Removes leading and trailing spaces from a string
TRIM
Truncates a number to the specified number of decimal places
TRUNCATE
Tversky index
Converts a string to upper-case
UCASE
unescape in the given escaping modes
unescape
Decodes a byte array in the UTF8 format to a string
UTF8TOSTRING
Variational distance
Returns the week number for a given date
WEEK
Returns the weekday number for a given date
WEEKDAY
Returns the week number for a given date
WEEKOFYEAR
Creates an XML attribute element of the form name=value
XMLATTR
Creates an XML CDATA element
XMLCDATA
Creates an XML comment
XMLCOMMENT
Create an XML node element
XMLNODE
Returns the XML declaration
XMLSTARTDOC
Creates an XML text element
XMLTEXT
Returns the year part for a given date
YEAR
Returns the year and week number for a given date
YEARWEEK
Returns the value 0
ZERO