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.

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 suppped 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