SAP HANA Introduction
- SAP HANA - Core Architecture
- SAP HANA - Information Modeler
- SAP HANA - System Monitor
- Studio Administration View
- SAP HANA - Studio
- In-Memory Computing Engine
- SAP HANA - Overview
SAP HANA Modeling
- SAP HANA - Export and Import
- SAP HANA - Information Composer
- SAP HANA - Analytic Privileges
- SAP HANA - Calculation View
- SAP HANA - Analytic View
- SAP HANA - Attribute View
- SAP HANA - Packages
- SAP HANA - Tables
- SAP HANA - Data Warehouse
- SAP HANA - Modeling
SAP HANA Reporting
- SAP HANA - Excel Integration
- SAP HANA - Crystal Reports
- Bi 4.0 Connectivity to HANA Views
- SAP HANA - Reporting View
SAP HANA Security
- SAP HANA - Auditing
- SAP HANA - License Management
- SAP HANA - Authorization methods
- SAP HANA - Authentications
- User Administration & Management
- SAP HANA - Security Overview
SAP HANA Data Replication
- SAP HANA - MDX Provider
- SAP HANA - CTL Method
- SAP HANA - DXC Method
- SAP HANA - Log Based Replication
- SAP HANA - ETL Based Replication
- SAP HANA - Data Replication Overview
SAP HANA Monitoring
- SAP HANA - Log Configuration
- SAP HANA - High Availability
- SAP HANA - Backup & Recovery
- SAP HANA - Persistent Layer
- SAP HANA - Monitoring and Alerting
SAP HANA SQL
- SAP HANA - SQL Script
- SAP HANA - SQL Data Profiling
- SAP HANA - SQL Explain Plans
- SAP HANA - SQL Synonym
- SAP HANA - SQL Triggers
- SAP HANA - SQL Sequences
- SAP HANA - SQL Stored Procedures
- SAP HANA - SQL Expressions
- SAP HANA - SQL Functions
- SAP HANA - SQL Operators
- SAP HANA - Data Types
- SAP HANA - SQL Overview
SAP HANA Useful Resources
- SAP HANA - Discussion
- SAP HANA - Useful Resources
- SAP HANA - Quick Guide
- SAP HANA - Questions and Answers
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
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