880 lines
10 KiB
Markdown
880 lines
10 KiB
Markdown
# Data Transformation Language (DTL) Functions Reference
|
|
|
|
Complete reference for Data Transformation Language functions in SAP Data Intelligence.
|
|
|
|
## Table of Contents
|
|
|
|
1. [Overview](#overview)
|
|
2. [String Functions](#string-functions)
|
|
3. [Numeric Functions](#numeric-functions)
|
|
4. [Date and Time Functions](#date-and-time-functions)
|
|
5. [Data Type Conversion Functions](#data-type-conversion-functions)
|
|
6. [Miscellaneous Functions](#miscellaneous-functions)
|
|
|
|
---
|
|
|
|
## Overview
|
|
|
|
Data Transformation Language (DTL) provides SQL-like functions for data processing in:
|
|
|
|
- Data Transform operator
|
|
- Data preparation tools
|
|
- Structured data operators
|
|
|
|
**Syntax Pattern:**
|
|
```
|
|
FUNCTION_NAME(argument1, argument2, ...)
|
|
```
|
|
|
|
---
|
|
|
|
## String Functions
|
|
|
|
Functions for extracting, manipulating, and returning string information.
|
|
|
|
### CONCAT
|
|
|
|
Concatenates two or more strings.
|
|
|
|
```sql
|
|
CONCAT(string1, string2 [, string3, ...])
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
CONCAT('Hello', ' ', 'World') -- Returns: 'Hello World'
|
|
```
|
|
|
|
### SUBSTRING
|
|
|
|
Extracts a portion of a string.
|
|
|
|
```sql
|
|
SUBSTRING(string, start_position [, length])
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
SUBSTRING('SAP Data Intelligence', 5, 4) -- Returns: 'Data'
|
|
```
|
|
|
|
### SUBSTRAFTER
|
|
|
|
Returns substring after the first occurrence of a delimiter.
|
|
|
|
```sql
|
|
SUBSTRAFTER(string, delimiter)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
SUBSTRAFTER('user@domain.com', '@') -- Returns: 'domain.com'
|
|
```
|
|
|
|
### SUBSTRBEFORE
|
|
|
|
Returns substring before the first occurrence of a delimiter.
|
|
|
|
```sql
|
|
SUBSTRBEFORE(string, delimiter)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
SUBSTRBEFORE('user@domain.com', '@') -- Returns: 'user'
|
|
```
|
|
|
|
### LEFT
|
|
|
|
Returns leftmost characters of a string.
|
|
|
|
```sql
|
|
LEFT(string, length)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
LEFT('SAP Data', 3) -- Returns: 'SAP'
|
|
```
|
|
|
|
### RIGHT
|
|
|
|
Returns rightmost characters of a string.
|
|
|
|
```sql
|
|
RIGHT(string, length)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
RIGHT('SAP Data', 4) -- Returns: 'Data'
|
|
```
|
|
|
|
### LENGTH
|
|
|
|
Returns the length of a string.
|
|
|
|
```sql
|
|
LENGTH(string)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
LENGTH('SAP') -- Returns: 3
|
|
```
|
|
|
|
### UPPER / UCASE
|
|
|
|
Converts string to uppercase.
|
|
|
|
```sql
|
|
UPPER(string)
|
|
UCASE(string)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
UPPER('sap data') -- Returns: 'SAP DATA'
|
|
```
|
|
|
|
### LOWER / LCASE
|
|
|
|
Converts string to lowercase.
|
|
|
|
```sql
|
|
LOWER(string)
|
|
LCASE(string)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
LOWER('SAP DATA') -- Returns: 'sap data'
|
|
```
|
|
|
|
### TRIM
|
|
|
|
Removes leading and trailing spaces.
|
|
|
|
```sql
|
|
TRIM(string)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
TRIM(' SAP ') -- Returns: 'SAP'
|
|
```
|
|
|
|
### LTRIM
|
|
|
|
Removes leading spaces.
|
|
|
|
```sql
|
|
LTRIM(string)
|
|
```
|
|
|
|
### RTRIM
|
|
|
|
Removes trailing spaces.
|
|
|
|
```sql
|
|
RTRIM(string)
|
|
```
|
|
|
|
### LPAD
|
|
|
|
Pads string on the left to specified length.
|
|
|
|
```sql
|
|
LPAD(string, length [, pad_string])
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
LPAD('42', 5, '0') -- Returns: '00042'
|
|
```
|
|
|
|
### RPAD
|
|
|
|
Pads string on the right to specified length.
|
|
|
|
```sql
|
|
RPAD(string, length [, pad_string])
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
RPAD('SAP', 6, 'X') -- Returns: 'SAPXXX'
|
|
```
|
|
|
|
### REPLACE
|
|
|
|
Replaces occurrences of a substring.
|
|
|
|
```sql
|
|
REPLACE(string, search_string, replace_string)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
REPLACE('SAP HANA', 'HANA', 'DI') -- Returns: 'SAP DI'
|
|
```
|
|
|
|
### LOCATE
|
|
|
|
Returns position of substring in string.
|
|
|
|
```sql
|
|
LOCATE(string, substring [, start_position])
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
LOCATE('SAP Data Intelligence', 'Data') -- Returns: 5
|
|
```
|
|
|
|
### ASCII
|
|
|
|
Returns ASCII code of first character.
|
|
|
|
```sql
|
|
ASCII(string)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
ASCII('A') -- Returns: 65
|
|
```
|
|
|
|
### CHAR
|
|
|
|
Returns character for ASCII code.
|
|
|
|
```sql
|
|
CHAR(integer)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
CHAR(65) -- Returns: 'A'
|
|
```
|
|
|
|
---
|
|
|
|
## Numeric Functions
|
|
|
|
Functions for mathematical operations on numeric data.
|
|
|
|
### ABS
|
|
|
|
Returns absolute value.
|
|
|
|
```sql
|
|
ABS(number)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
ABS(-42) -- Returns: 42
|
|
```
|
|
|
|
### CEIL
|
|
|
|
Rounds up to nearest integer.
|
|
|
|
```sql
|
|
CEIL(number)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
CEIL(4.2) -- Returns: 5
|
|
```
|
|
|
|
### FLOOR
|
|
|
|
Rounds down to nearest integer.
|
|
|
|
```sql
|
|
FLOOR(number)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
FLOOR(4.8) -- Returns: 4
|
|
```
|
|
|
|
### ROUND
|
|
|
|
Rounds to specified decimal places.
|
|
|
|
```sql
|
|
ROUND(number [, decimal_places])
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
ROUND(3.14159, 2) -- Returns: 3.14
|
|
```
|
|
|
|
### MOD
|
|
|
|
Returns remainder of division.
|
|
|
|
```sql
|
|
MOD(dividend, divisor)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
MOD(10, 3) -- Returns: 1
|
|
```
|
|
|
|
### POWER
|
|
|
|
Returns base raised to exponent.
|
|
|
|
```sql
|
|
POWER(base, exponent)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
POWER(2, 3) -- Returns: 8
|
|
```
|
|
|
|
### SQRT
|
|
|
|
Returns square root.
|
|
|
|
```sql
|
|
SQRT(number)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
SQRT(16) -- Returns: 4
|
|
```
|
|
|
|
### EXP
|
|
|
|
Returns e raised to power.
|
|
|
|
```sql
|
|
EXP(number)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
EXP(1) -- Returns: 2.71828...
|
|
```
|
|
|
|
### LN
|
|
|
|
Returns natural logarithm.
|
|
|
|
```sql
|
|
LN(number)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
LN(2.71828) -- Returns: ~1
|
|
```
|
|
|
|
### LOG
|
|
|
|
Returns logarithm with specified base.
|
|
|
|
```sql
|
|
LOG(base, number)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
LOG(10, 100) -- Returns: 2
|
|
```
|
|
|
|
### SIGN
|
|
|
|
Returns sign of number (-1, 0, or 1).
|
|
|
|
```sql
|
|
SIGN(number)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
SIGN(-42) -- Returns: -1
|
|
```
|
|
|
|
### UMINUS
|
|
|
|
Returns negation of number.
|
|
|
|
```sql
|
|
UMINUS(number)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
UMINUS(42) -- Returns: -42
|
|
```
|
|
|
|
### RAND
|
|
|
|
Returns random number between 0 and 1.
|
|
|
|
```sql
|
|
RAND()
|
|
```
|
|
|
|
---
|
|
|
|
## Date and Time Functions
|
|
|
|
Functions for date and time operations.
|
|
|
|
### CURRENT_UTCDATE
|
|
|
|
Returns current UTC date.
|
|
|
|
```sql
|
|
CURRENT_UTCDATE()
|
|
```
|
|
|
|
### CURRENT_UTCTIME
|
|
|
|
Returns current UTC time.
|
|
|
|
```sql
|
|
CURRENT_UTCTIME()
|
|
```
|
|
|
|
### CURRENT_UTCTIMESTAMP
|
|
|
|
Returns current UTC timestamp.
|
|
|
|
```sql
|
|
CURRENT_UTCTIMESTAMP()
|
|
```
|
|
|
|
### ADD_DAYS
|
|
|
|
Adds days to a date.
|
|
|
|
```sql
|
|
ADD_DAYS(date, days)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
ADD_DAYS('2025-01-01', 30) -- Returns: '2025-01-31'
|
|
```
|
|
|
|
### ADD_MONTHS
|
|
|
|
Adds months to a date.
|
|
|
|
```sql
|
|
ADD_MONTHS(date, months)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
ADD_MONTHS('2025-01-15', 2) -- Returns: '2025-03-15'
|
|
```
|
|
|
|
### ADD_YEARS
|
|
|
|
Adds years to a date.
|
|
|
|
```sql
|
|
ADD_YEARS(date, years)
|
|
```
|
|
|
|
### ADD_SECONDS
|
|
|
|
Adds seconds to a timestamp.
|
|
|
|
```sql
|
|
ADD_SECONDS(timestamp, seconds)
|
|
```
|
|
|
|
### DAYS_BETWEEN
|
|
|
|
Returns days between two dates.
|
|
|
|
```sql
|
|
DAYS_BETWEEN(date1, date2)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
DAYS_BETWEEN('2025-01-01', '2025-01-31') -- Returns: 30
|
|
```
|
|
|
|
### MONTHS_BETWEEN
|
|
|
|
Returns months between two dates.
|
|
|
|
```sql
|
|
MONTHS_BETWEEN(date1, date2)
|
|
```
|
|
|
|
### YEARS_BETWEEN
|
|
|
|
Returns years between two dates.
|
|
|
|
```sql
|
|
YEARS_BETWEEN(date1, date2)
|
|
```
|
|
|
|
### SECONDS_BETWEEN
|
|
|
|
Returns seconds between two timestamps.
|
|
|
|
```sql
|
|
SECONDS_BETWEEN(timestamp1, timestamp2)
|
|
```
|
|
|
|
### EXTRACT
|
|
|
|
Extracts date/time component.
|
|
|
|
```sql
|
|
EXTRACT(component FROM date_or_timestamp)
|
|
```
|
|
|
|
**Components:** YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
|
|
|
|
**Example:**
|
|
```sql
|
|
EXTRACT(YEAR FROM '2025-06-15') -- Returns: 2025
|
|
```
|
|
|
|
### YEAR
|
|
|
|
Returns year from date.
|
|
|
|
```sql
|
|
YEAR(date)
|
|
```
|
|
|
|
### MONTH
|
|
|
|
Returns month from date (1-12).
|
|
|
|
```sql
|
|
MONTH(date)
|
|
```
|
|
|
|
### DAYOFMONTH
|
|
|
|
Returns day of month (1-31).
|
|
|
|
```sql
|
|
DAYOFMONTH(date)
|
|
```
|
|
|
|
### DAYOFYEAR
|
|
|
|
Returns day of year (1-366).
|
|
|
|
```sql
|
|
DAYOFYEAR(date)
|
|
```
|
|
|
|
### WEEK
|
|
|
|
Returns week number (1-53).
|
|
|
|
```sql
|
|
WEEK(date)
|
|
```
|
|
|
|
### ISOWEEK
|
|
|
|
Returns ISO week number.
|
|
|
|
```sql
|
|
ISOWEEK(date)
|
|
```
|
|
|
|
### QUARTER
|
|
|
|
Returns quarter (1-4).
|
|
|
|
```sql
|
|
QUARTER(date)
|
|
```
|
|
|
|
### HOUR
|
|
|
|
Returns hour (0-23).
|
|
|
|
```sql
|
|
HOUR(timestamp)
|
|
```
|
|
|
|
### MINUTE
|
|
|
|
Returns minute (0-59).
|
|
|
|
```sql
|
|
MINUTE(timestamp)
|
|
```
|
|
|
|
### SECOND
|
|
|
|
Returns second (0-59).
|
|
|
|
```sql
|
|
SECOND(timestamp)
|
|
```
|
|
|
|
### DAYNAME
|
|
|
|
Returns day name.
|
|
|
|
```sql
|
|
DAYNAME(date)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
DAYNAME('2025-01-01') -- Returns: 'Wednesday'
|
|
```
|
|
|
|
### MONTHNAME
|
|
|
|
Returns month name.
|
|
|
|
```sql
|
|
MONTHNAME(date)
|
|
```
|
|
|
|
### LAST_DAY
|
|
|
|
Returns last day of month.
|
|
|
|
```sql
|
|
LAST_DAY(date)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
LAST_DAY('2025-02-15') -- Returns: '2025-02-28'
|
|
```
|
|
|
|
### NEXT_DAY
|
|
|
|
Returns next occurrence of weekday.
|
|
|
|
```sql
|
|
NEXT_DAY(date, weekday)
|
|
```
|
|
|
|
---
|
|
|
|
## Data Type Conversion Functions
|
|
|
|
Functions for converting between data types.
|
|
|
|
### TO_STRING
|
|
|
|
Converts to string.
|
|
|
|
```sql
|
|
TO_STRING(value [, format])
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
TO_STRING(12345) -- Returns: '12345'
|
|
TO_STRING(3.14, '0.00') -- Returns: '3.14'
|
|
```
|
|
|
|
### TO_INTEGER
|
|
|
|
Converts to integer.
|
|
|
|
```sql
|
|
TO_INTEGER(value)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
TO_INTEGER('42') -- Returns: 42
|
|
TO_INTEGER(3.7) -- Returns: 3
|
|
```
|
|
|
|
### TO_DECIMAL
|
|
|
|
Converts to decimal.
|
|
|
|
```sql
|
|
TO_DECIMAL(value [, precision, scale])
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
TO_DECIMAL('123.45', 10, 2) -- Returns: 123.45
|
|
```
|
|
|
|
### TO_FLOATING
|
|
|
|
Converts to floating point.
|
|
|
|
```sql
|
|
TO_FLOATING(value)
|
|
```
|
|
|
|
### TO_DATE
|
|
|
|
Converts to date.
|
|
|
|
```sql
|
|
TO_DATE(string [, format])
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
TO_DATE('2025-01-15', 'YYYY-MM-DD')
|
|
TO_DATE('15/01/2025', 'DD/MM/YYYY')
|
|
```
|
|
|
|
### TO_TIME
|
|
|
|
Converts to time.
|
|
|
|
```sql
|
|
TO_TIME(string [, format])
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
TO_TIME('14:30:00', 'HH24:MI:SS')
|
|
```
|
|
|
|
### TO_DATETIME
|
|
|
|
Converts to datetime/timestamp.
|
|
|
|
```sql
|
|
TO_DATETIME(string [, format])
|
|
```
|
|
|
|
---
|
|
|
|
## Miscellaneous Functions
|
|
|
|
### CASE
|
|
|
|
Conditional expression.
|
|
|
|
```sql
|
|
CASE
|
|
WHEN condition1 THEN result1
|
|
WHEN condition2 THEN result2
|
|
ELSE default_result
|
|
END
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
CASE
|
|
WHEN status = 'A' THEN 'Active'
|
|
WHEN status = 'I' THEN 'Inactive'
|
|
ELSE 'Unknown'
|
|
END
|
|
```
|
|
|
|
### COALESCE
|
|
|
|
Returns first non-null value.
|
|
|
|
```sql
|
|
COALESCE(value1, value2, ...)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
COALESCE(phone, mobile, 'No number') -- Returns first non-null
|
|
```
|
|
|
|
### IFNULL
|
|
|
|
Returns second value if first is null.
|
|
|
|
```sql
|
|
IFNULL(value, replacement)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
IFNULL(discount, 0) -- Returns 0 if discount is null
|
|
```
|
|
|
|
### NULLIF
|
|
|
|
Returns null if values are equal.
|
|
|
|
```sql
|
|
NULLIF(value1, value2)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
NULLIF(quantity, 0) -- Returns null if quantity is 0
|
|
```
|
|
|
|
### GREATEST
|
|
|
|
Returns largest value.
|
|
|
|
```sql
|
|
GREATEST(value1, value2, ...)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
GREATEST(10, 20, 15) -- Returns: 20
|
|
```
|
|
|
|
### LEAST
|
|
|
|
Returns smallest value.
|
|
|
|
```sql
|
|
LEAST(value1, value2, ...)
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
LEAST(10, 20, 15) -- Returns: 10
|
|
```
|
|
|
|
### MAP
|
|
|
|
Maps value to another value.
|
|
|
|
```sql
|
|
MAP(input, value1, result1 [, value2, result2, ...] [, default])
|
|
```
|
|
|
|
**Example:**
|
|
```sql
|
|
MAP(status, 'A', 'Active', 'I', 'Inactive', 'Unknown')
|
|
```
|
|
|
|
---
|
|
|
|
## Documentation Links
|
|
|
|
- **Function Reference**: [https://github.com/SAP-docs/sap-hana-cloud-data-intelligence/tree/main/docs/functionreference](https://github.com/SAP-docs/sap-hana-cloud-data-intelligence/tree/main/docs/functionreference)
|
|
- **DTL Functions**: [https://github.com/SAP-docs/sap-hana-cloud-data-intelligence/tree/main/docs/functionreference/function-reference-for-data-transformation-language](https://github.com/SAP-docs/sap-hana-cloud-data-intelligence/tree/main/docs/functionreference/function-reference-for-data-transformation-language)
|
|
|
|
---
|
|
|
|
**Last Updated**: 2025-11-22
|