SQL Query Techniques: Isolating Substrings in SAP HANA

SQL Query Techniques: Isolating Substrings in SAP HANA

In SAP HANA SQL, SUBSTR_BEFORE and SUBSTR_AFTER are the two functions that help in pulling out parts of words or phrases. They are essential for data manipulation and analysis.

SUBSTR_BEFORE grabs everything before a certain word or symbol, while SUBSTR_AFTER gets everything after it.

Syntax for the functions:

SUBSTR_BEFORE function

SUBSTR_BEFORE (string_expression, delimiter)

•  string_expression: Refers to the column name or a string value from which you want to extract a substring.
•  delimiter: The character or substring used as the delimiter to identify where to split the string. The function returns the part of `string_expression` before the first occurrence of `delimiter`.

SUBSTR_AFTER function

SUBSTR_AFTER (string_expression, delimiter)

•   string_expression: Refers to the column name or a string value from which you want to extract a substring.
•   delimiter: The character or substring used as the delimiter to identify where to split the string. The function returns the part of `string_expression` after the first occurrence of `delimiter`.

These functions are particularly valuable in scenarios where strings contain structured data, such as codes or identifiers separated by consistent patterns. For instance, if you have a bunch of order numbers that all contain ‘MR’, the SUBSTR_BEFORE function can quickly extract the numbers before ‘MR’. And if there’s any additional information after ‘MR’, then SUBSTR_AFTER can grab that part.

Example

In the provided table, we extract substrings from the column ‘U_AVA_GFREFNO’, isolating portions both before and after the delimiter ‘MR’.

SQL Queries for extracting substrings for the above:

SELECT A.”U_AVA_GFREFNO”, SUBSTR_BEFORE (A.”U_AVA_GFREFNO”, ‘MR’) as “OPDN_MRNNO_Before”, SUBSTR_AFTER (A.”U_AVA_GFREFNO”, ‘MR’) as “OPDN_MRNNO_After” FROM “PDN1”;

Closing Remarks on Text Data Manipulation in SAP HANA

These functions are useful for extracting segments of strings using a defined delimiter, enabling detailed manipulation of text data within SQL queries. Whether you’re breaking down codes or picking out key details, SUBSTR_BEFORE and SUBSTR_AFTER make SAP HANA SQL simpler and more powerful for getting the info you need from your text.