Substring in SAP HANA Calculated Column – Example and Syntax

by

In SAP HANA, you can use the SUBSTRING function inside a calculated column to extract a portion of a text value. This is commonly used in modeling views (Calculation Views) to split codes, extract date parts, or clean up identifiers before reporting.

When to Use SUBSTRING in SAP HANA

You use substring logic in calculated columns to

  • Extract plant or company code from material or location IDs.
  • Derive year or month from date strings.
  • Remove prefixes or suffixes from customer or product codes.
  • Simplify data for analytics or joins.

Syntax of SUBSTRING Function

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

Parameters:

  • <string_expression> → Column or text value.
  • <start_position> → Position where extraction begins (1-based index).
  • <length> → Number of characters to extract.

Example 1: Extract First Four Characters

If you have a column CUSTOMER_ID with values like CUST1001, and you only want CUST:

SUBSTRING("CUSTOMER_ID", 1, 4)

Result:

CUSTOMER_IDResult
CUST1001CUST
CUST2002CUST

Example 2: Extract Numeric Part from ID

To get the numeric portion after the prefix, you can combine LENGTH() and POSITION() with SUBSTRING():

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

If CUSTOMER_ID = 'CUST_12345',
Result = 12345

Also Read: Groovy Script in SAP CPI Message Mapping – Complete Guide for Beginners

Example 3: Extract Year from a Date String

If you have a column ORDER_DATE stored as text 20250310 (YYYYMMDD), extract the year and month using:

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

Result:

ORDER_DATEYEARMONTH
20250310202503

Example 4: Use in Calculated Column in SAP HANA Modeler

In SAP HANA Calculation View (Modeler or Web IDE):

  1. Open your view in Calculation View Editor.
  2. Go to the Output or Projection Node.
  3. Click Add Calculated Column.
  4. In the Expression Editor, type: SUBSTRING("MATERIAL_ID", 1, 5)
  5. Give the column a name like MATERIAL_PREFIX.
  6. Save and activate the view.

When you preview data, you’ll see the calculated substring result for each record.

Example 5: Extract Plant Code from Storage Location ID

If your field STORAGE_LOC has a structure like PL01_LOC123, you can split it as:

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

This separates both parts for use in reports or joins.

Also Read:Groovy Script for Substring in SAP CPI – Simple Examples

Best Practices

  • HANA’s SUBSTRING index starts at 1, not 0.
  • Always confirm field lengths before defining substrings to avoid truncation.
  • Use functions like POSITION() to make extraction dynamic.
  • Combine with CASE or INSTR if your string pattern varies.

Summary

The SUBSTRING function in SAP HANA calculated columns is a simple yet powerful way to extract or format text data at the modeling layer. Whether you are preparing data for analytics, reporting, or joins, using substring logic helps you simplify and standardize field values efficiently.

Example Quick Reference:

SUBSTRING("COLUMN_NAME", 1, 5)

Extracts the first five characters from the column in any SAP HANA calculation view.

Leave a Comment