SAP HANA SQL Functions: Types & Examples

SAP HANA SQL Functions

SAP HANA Provides following SAP HANA Functions-

1. Data Type Conversion Function – Data Type conversion function are used to convert one data type to another. Below are list of Data Type Conversion function-E.g. CAST, TO_ALPHANUM, TO_BIGINT, TO_BINARY etc.
2. Date Time Functions – Date Time Function are used to convert Date/ Time in a different format. E.g. – ADD_DAYS, ADD_MONTHS, ADD_SECOND, etc.
3. Fulltext Functions – Fulltext Functions is used for text search. E.g. – SCORE etc.
4. Number Functions – Number Functions take a numeric value, or string with numeric characters, as input and return numeric values. E.g. – ABS, ROUND, POWER, etc.
5. String Functions – String Functions take a string as input, process them and return value according to function. E.g. (ASCII, CHAR, CONCAT, etc.)
6. Window Functions – Window Functions let user divide result set of a query into groups of rows named window partition. E.g. RANK (), DENSE_RANK (), ROW_NUMBER (), etc.
7. Miscellaneous Function- There are some more functions, which are used for the miscellaneous job. E.g. – CONVERT_CURRENCY, CURRENT_SCHEMA, etc.

Every function above is evaluated inside the database engine, which is why pushing logic into a function is almost always faster than fetching rows and processing them in an application. The examples below use the DUMMY table, a single row system table that exists purely so an expression can be tested without touching real data.

Scalar, Aggregate and Window Functions

Before the examples, one distinction decides how a function behaves in a query: how many rows go in, and how many come out.

The practical consequence is that an aggregate collapses detail while a window function preserves it. Asking for each employee alongside their department average needs a window function, because a GROUP BY would remove the individual rows entirely.

Common SAP HANA Functions with Examples

The statements below can be pasted straight into the SQL console. Each demonstrates one category from the list above.

1) String functions. These take character input and return a derived value.

SELECT
  UPPER('sap hana')               AS UPPER_TEXT,
  LENGTH('SAP HANA')              AS TEXT_LENGTH,
  CONCAT('SAP ', 'HANA')         AS JOINED,
  SUBSTRING('SAP HANA', 5, 4)   AS PART,
  REPLACE('SAP HANA', 'HANA', 'S/4') AS SWAPPED,
  TRIM('  padded  ')             AS CLEANED
FROM DUMMY;

2) Number functions. Rounding behaviour is worth testing, because ROUND uses commercial rounding while FLOOR and CEIL always move in one direction.

SELECT
  ABS(-45.7)          AS ABSOLUTE_VALUE,
  ROUND(123.4567, 2) AS ROUNDED,
  FLOOR(9.9)          AS ROUNDED_DOWN,
  CEIL(9.1)           AS ROUNDED_UP,
  POWER(2, 10)       AS RAISED,
  MOD(17, 5)         AS REMAINDER
FROM DUMMY;

3) Date and time functions. These are the ones most often needed for period reporting.

SELECT
  CURRENT_DATE                              AS TODAY,
  ADD_DAYS(CURRENT_DATE, 30)                AS IN_30_DAYS,
  ADD_MONTHS(CURRENT_DATE, -1)              AS LAST_MONTH,
  DAYS_BETWEEN('2026-01-01', CURRENT_DATE) AS ELAPSED,
  LAST_DAY(CURRENT_DATE)                    AS MONTH_END,
  WEEKDAY(CURRENT_DATE)                     AS DAY_INDEX
FROM DUMMY;

4) Conversion functions. Explicit conversion prevents the silent type coercion that slows a WHERE clause.

SELECT
  CAST('1234' AS INTEGER)               AS AS_NUMBER,
  TO_VARCHAR(CURRENT_DATE, 'DD.MM.YYYY') AS AS_TEXT,
  TO_DECIMAL(1234.5678, 10, 2)         AS AS_DECIMAL
FROM DUMMY;

Type behaviour and the limits of each target type are covered in SAP HANA data types.

Window Functions Explained

A window function performs a calculation across a set of rows related to the current row, without collapsing them. The OVER clause defines that set, and it has two optional parts: PARTITION BY splits the data into groups, and ORDER BY fixes the sequence inside each group.

SELECT
  DEPARTMENT,
  EMP_NAME,
  SALARY,
  ROW_NUMBER() OVER (PARTITION BY DEPARTMENT ORDER BY SALARY DESC) AS ROW_NUM,
  RANK()       OVER (PARTITION BY DEPARTMENT ORDER BY SALARY DESC) AS RANKING,
  DENSE_RANK() OVER (PARTITION BY DEPARTMENT ORDER BY SALARY DESC) AS DENSE_RANKING,
  SUM(SALARY)  OVER (PARTITION BY DEPARTMENT) AS DEPT_TOTAL
FROM DHK_SCHEMA.EMPLOYEE;

The three ranking functions differ only when values tie:

• ROW_NUMBER never ties. Two identical salaries still receive 1 and 2, chosen arbitrarily.
• RANK gives tied rows the same number and then skips. Two rows at 1 are followed by 3.
• DENSE_RANK gives tied rows the same number and does not skip. Two rows at 1 are followed by 2.

Use ROW_NUMBER to deduplicate, RANK for competition style placing, and DENSE_RANK when the sequence must stay unbroken. Comparison and logical symbols used alongside these functions are covered in SAP HANA operators, and statement structure in SAP HANA SQL.

FAQs