SAP HANA - SQL Functions
There are various SQL functions provided by SAP HANA database −
- Numeric Functions
- String Functions
- Fulltext Functions
- Datetime Functions
- Aggregate Functions
- Data Type Conversion Functions
- Window Functions
- Series Data Functions
- Miscellaneous Functions
Numeric Functions
These are inbuilt numeric functions in SQL and use in scripting. It takes numeric values or strings with numeric characters and return numeric values.
Example − SELECT ABS (-1) "abs" FROM TEST;
abs
1
ACOS, ASIN, ATAN, ATAN2 (These functions return trigonometric value of the argument)
BINTOHEX − It converts a Binary value to a hexadecimal value.
BITAND − It performs an AND operation on bits of passed argument.
BITCOUNT − It performs the count of number of set bits in an argument.
BITNOT − It performs a bitwise NOT operation on the bits of argument.
BITOR − It perform an OR operation on bits of passed argument.
BITSET − It is used to set bits to 1 in <target_num> from the <start_bit> position.
BITUNSET − It is used to set bits to 0 in <target_num> from the <start_bit> position.
BITXOR − It performs XOR operation on bits of passed argument.
CEIL − It returns the first integer that is greater or equal to the passed value.
COS, COSH, COT ((These functions return trigonometric value of the argument)
EXP − It returns the result of the base of natural logarithms e raised to the power of passed value.
FLOOR − It returns the largest integer not greater than the numeric argument.
HEXTOBIN − It converts a hexadecimal value to a binary value.
LN − It returns the natural logarithm of the argument.
LOG − It returns the algorithm value of a passed positive value. Both base and log value should be positive.
Various other numeric functions can also be used − MOD, POWER, RAND, ROUND, SIGN, SIN, SINH, SQRT, TAN, TANH, UMINUS
String Functions
Various SQL string functions can be used in HANA with SQL scripting. Most common string functions are −
ASCII − It returns integer ASCII value of passed string.
CHAR − It returns the character associated with passed ASCII value.
CONCAT − It is Concatenation operator and returns the combined passed strings.
LCASE − It converts all character of a string to Lower case.
LEFT − It returns the first characters of a passed string as per mentioned value.
LENGTH − It returns the number of characters in passed string.
LOCATE − It returns the position of substring within passed string.
LOWER − It converts all characters in string to lowercase.
NCHAR − It returns the Unicode character with passed integer value.
REPLACE − It searches in passed original string for all occurrences of search string and replaces them with replace string.
RIGHT − It returns the rightmost passed value characters of mentioned string.
UPPER − It converts all characters in passed string to uppercase.
UCASE − It is identical to UPPER function. It converts all characters in passed string to uppercase.
Other string functions that can be used are − LPAD, LTRIM, RTRIM, STRTOBIN, SUBSTR_AFTER, SUBSTR_BEFORE, SUBSTRING, TRIM, UNICODE, RPAD, BINTOSTR
Date Time functions
There are various Date Time functions that can be used in HANA in SQL scripts. Most common Date Time functions are −
CURRENT_DATE − It returns the current local system date.
CURRENT_TIME − It returns the current local system time.
CURRENT_TIMESTAMP − It returns the current local system timestamp details (YYYY-MM-DD HH:MM:SS:FF).
CURRENT_UTCDATE − It returns current UTC (Greenwich Mean date) date.
CURRENT_UTCTIME − It returns current UTC (Greenwich Mean Time) time.
CURRENT_UTCTIMESTAMP
DAYOFMONTH − It returns the integer value of day in passed date in argument.
HOUR − It returns integer value of hour in passed time in argument.
YEAR − It returns the year value of passed date.
Other Date Time functions are − DAYOFYEAR, DAYNAME, DAYS_BETWEEN, EXTRACT, NANO100_BETWEEN, NEXT_DAY, NOW, QUARTER, SECOND, SECONDS_BETWEEN, UTCTOLOCAL, WEEK, WEEKDAY, WORKDAYS_BETWEEN, ISOWEEK, LAST_DAY, LOCALTOUTC, MINUTE, MONTH, MONTHNAME, ADD_DAYS, ADD_MONTHS, ADD_SECONDS, ADD_WORKDAYS
Data Type Conversion Functions
These functions are used to convert one data type to other or to perform a check if conversion is possible or not.
Most common data type conversion functions used in HANA in SQL scripts −
CAST − It returns the value of an expression converted to a supplied data type.
TO_ALPHANUM − It converts a passed value to an ALPHANUM data type
TO_REAL − It converts a value to a REAL data type.
TO_TIME − It converts a passed time string to the TIME data type.
TO_CLOB − It converts a value to a CLOB data type.
Other similar Data Type conversion functions are − TO_BIGINT, TO_BINARY, TO_BLOB, TO_DATE, TO_DATS, TO_DECIMAL, TO_DOUBLE, TO_FIXEDCHAR, TO_INT, TO_INTEGER, TO_NCLOB, TO_NVARCHAR, TO_TIMESTAMP, TO_TINYINT, TO_VARCHAR, TO_SECONDDATE, TO_SMALLDECIMAL, TO_SMALLINT
There are also various Windows and other miscellaneous functions that can be used in HANA SQL scripts.
