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