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.
- ABS − It returns the absolute value of a numeric argument.
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.
- Current_Schema − It returns a string containing the current schema name.
- Session_User − It returns the user name of current session
