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

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*