Substring in SAP HANA Calculated Column – Example and Syntax

by

Substring in SAP HANA Calculated Column – Example and Syntax

In SAP HANA, the SUBSTRING function is commonly used in calculated columns to extract a specific portion of a string. It’s especially useful when you need to split codes, extract date parts, or derive segments from IDs in a calculation view or SQL-based model.

This guide explains the syntax, examples, and best practices for using the SUBSTRING function inside calculated columns in SAP HANA.

When to Use SUBSTRING in SAP HANA

You typically use substring logic in calculated columns when you want to

  • Extract prefixes or suffixes from material or customer codes.
  • Derive year, month, or day from a date string.
  • Split plant or storage location codes.
  • Remove unwanted characters or padding.
  • Simplify field values for reporting or joins.

Also Read: SAP Business Data Cloud Cockpit Tutorial – Step-by-Step Guide

SUBSTRING Function Syntax

SUBSTRING(<string_expression>, <start_position>, <length>)

Parameters

  • <string_expression> – The text or column name you want to extract from.
  • <start_position> – The starting character position (HANA indexing starts from 1).
  • <length> – The number of characters to extract.

Example 1: Extract the First Four Characters

If your column CUSTOMER_ID contains values like CUST1001, you can extract the prefix using:

SUBSTRING("CUSTOMER_ID", 1, 4)

Result

CUSTOMER_IDResult
CUST1001CUST
CUST2002CUST

Example 2: Extract Numeric Part from an ID

If your field contains both text and numbers, for example CUST_12345, you can extract only the numeric portion using:

SUBSTRING("CUSTOMER_ID", POSITION('_' IN "CUSTOMER_ID") + 1, LENGTH("CUSTOMER_ID"))

Result

CUSTOMER_IDResult
CUST_1234512345
CUST_6789067890

Here, POSITION() finds the underscore’s index, and LENGTH() defines the remaining characters to extract.

Also Read: How Does SAP Cloud ALM for Operations Enable Customers to Increase Operational Efficiency

Example 3: Extract Year and Month from Date String

If the ORDER_DATE field is stored as YYYYMMDD (e.g., 20250310), you can extract parts of it using:

SUBSTRING("ORDER_DATE", 1, 4) AS "YEAR",
SUBSTRING("ORDER_DATE", 5, 2) AS "MONTH",
SUBSTRING("ORDER_DATE", 7, 2) AS "DAY"

Result

ORDER_DATEYEARMONTHDAY
2025031020250310

Example 4: Using SUBSTRING in SAP HANA Calculation View

To create a calculated column using SUBSTRING in SAP Web IDE or HANA Studio:

  1. Open your Calculation View.
  2. Go to the Projection or Output node.
  3. Click Add Calculated Column.
  4. In the Expression Editor, enter: SUBSTRING("MATERIAL_ID", 1, 5)
  5. Name the column MATERIAL_PREFIX.
  6. Save and activate the view.
  7. Preview the data to verify the result.

Example 5: Extract Plant and Location Codes

If your field STORAGE_LOC is structured like PL01_LOC123, you can extract both parts using:

SUBSTRING("STORAGE_LOC", 1, 4) AS "PLANT_CODE",
SUBSTRING("STORAGE_LOC", 6, 3) AS "LOCATION_CODE"

Result

STORAGE_LOCPLANT_CODELOCATION_CODE
PL01_LOC123PL01LOC

Best Practices

  • SAP HANA substring indexing starts at 1, not 0.
  • Always confirm string length using LENGTH() before defining substring ranges.
  • Use POSITION() for dynamic extraction when delimiters vary.
  • Combine with CASE or INSTR for complex string logic.
  • Avoid nested substrings in calculated columns with large datasets for performance reasons.

Summary

The SUBSTRING function in SAP HANA calculated columns allows you to manipulate and extract string segments directly in the data model. It helps simplify transformations at the database level and improves data quality for analytics and reporting.

Quick Reference Example:

SUBSTRING("COLUMN_NAME", 1, 5)

This extracts the first five characters from any string column in your SAP HANA calculation view.

Leave a Comment